Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Oct 2003
    Posts
    8

    Unanswered: About the first Day and the last Day

    How should I do to get the first day and the last for a given month such as '200310' or '200309' and so on ? Please help me !

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    select dateadd(dd,-1*datepart(dd,@d)-1,@d)
    select dateadd(mm,1,dateadd(dd,-1*datepart(dd,@d)-1,@d))

  3. #3
    Join Date
    Oct 2003
    Location
    Pune
    Posts
    59

    Thumbs down

    it is not working!!!!!!!!!!!
    I tried it

    cyrus
    Cyrus
    Finding ways for solution

  4. #4
    Join Date
    Oct 2003
    Location
    Pune
    Posts
    59
    Can u specify ur question properly????
    First day for any month is always 1 and tnow the question is to find a last day of a month that can be done!!!!!
    but can u tell what u want

    Cyrus
    Cyrus
    Finding ways for solution

  5. #5
    Join Date
    Oct 2003
    Posts
    8
    If I past a string '200309',it should return the '20030901' and '20030930' to me. My question is something like the description above. The starting string for a month can be variable. Is all above clear to describe my question ?

  6. #6
    Join Date
    Oct 2003
    Location
    Pune
    Posts
    59
    This two statement will solve ur problem

    for last day
    select dateadd(mm,1,dateadd(dd,-1,'200310'+'01'))


    for first day

    select '200310'+'01'

    if u require explanation tell me
    Cyrus
    Finding ways for solution

  7. #7
    Join Date
    Oct 2003
    Posts
    8

    Thumbs up

    Thanks a lot, Cyrus! I will try the statements you just show me. I have just resolved my problem to use a While loop. I left the procedure on my computer, I will show you the statements tomorrow. Thank you again for your help.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by cyrus
    it is not working!!!!!!!!!!!
    I tried it

    cyrus


    DECLARE @d int
    SELECT @d = 1

    select dateadd(dd,-1*datepart(dd,@d)-1,@d)
    select dateadd(mm,1,dateadd(dd,-1*datepart(dd,@d)-1,@d))


    You need to supply the local variable

    Now try it...(and please cut and paste)
    Last edited by Brett Kaiser; 10-15-03 at 12:28.

  9. #9
    Join Date
    Oct 2003
    Location
    Pune
    Posts
    59
    Mr. Brett Kaiser
    thanks for ur advice.
    I have executed the query successfully without u help and then reached a conclusion that the query was not working right.

    Thanks for ur unneedful advice

    Cyrus
    Cyrus
    Finding ways for solution

  10. #10
    Join Date
    Oct 2003
    Posts
    8
    I am sorry to reply so late. The statements I wrote yesterday as below:

    declare @startday char(8)
    declare @startdate datetime
    declare @enddate datetime
    DECLARE @DAILY CHAR(2)
    declare @today datetime
    DECLARE @NEXTDAY DATETIME
    -- @ym is the variable of Year_Month in yyyymm
    set @startday = @ym + '01'
    set @startdate = convert(datetime,@startday)

    -- Is there any month not starting with '01' ?
    set @startdate = convert(char(10),@startdate,121) + ' 07:00:00'

    set @today = @startdate

    while month(@today) <= month(@startdate)
    begin
    set @today = @today + 1
    end

    set @enddate = convert(char(10),@today,121) + ' 07:00:00'

    Select date_mark from history where some_date >= @startdate and some_date < @enddate

    Is there any advice from you ? Thanks again, and also thanks to Kaiser.

  11. #11
    Join Date
    Oct 2003
    Location
    Pune
    Posts
    59
    What do u want to do mosu ?
    Is ur query trying to find records between start date and end date?

    Cyrus
    Cyrus
    Finding ways for solution

  12. #12
    Join Date
    Oct 2003
    Posts
    8
    Hi,Cyrus,
    What you think is exactly correct. I try to create a report what's duration is one month given by user. It is unknown what month will be selected until user selects one.
    By the way, I had tried your method yesterday. The result is as follow.

    select dateadd(mm,1,dateadd(dd,-1,'200310'+'01'))

    the result it returned is '2003-10-30 00:00:00.000' !

    It will not give me the last day of October, I think it should be '2003-10-31 00:00:00'. But I still thank you for your patience to try out my problem so enthusiastically.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the reason you got october 30 is because that formula is flawed

    it started with '200310'+'01', then subtracted a day, then added a month

    what you should to do get the last day of the current month is start with '200310'+'01', then add a month, then subtract a day


    easy, innit



    rudy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Oct 2003
    Location
    Pune
    Posts
    59
    its this mosu.

    select dateadd(dd,-1,dateadd(mm,1,'200302'+'01'))

    hope this is right
    Cyrus
    Finding ways for solution

  15. #15
    Join Date
    Oct 2003
    Location
    Pune
    Posts
    59
    You can put a condition as follow
    But here take care that month is always two digit so concat '0' when it is singel digit


    where ltrim(str(year(startdate))) + ltrim(str(month(startdate))) ='200203'
    Cyrus
    Finding ways for solution

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •