Post reply

How to find the last of a date field

  • May 20, 2003 at 6:52 pm

    #81622

    Hi,

    I am a new bee for store procedure. Can someone tell me how to find the last day for a date field?

    Thanks,

    yp

  • May 21, 2003 at 1:35 am

    #457921

    Hi,

    do you mean MAX(datefield)?

    or datepart(dd,datefield)?

    or a combination like SELECT datepart(dd,max(datefield)) from table?

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • May 21, 2003 at 8:52 pm

    #458082

    Hi Frank,

    Sorry I didn't make it clear in my original question. It's a monthly batch job. I will get the date when the job run. What I am looking for is the last day for the month and the year of the date because I need to come up with year-mm-lastday in my WHERE clause as the endday.

    Do you know any functions or source code to calculate the last day for the month based on a date?

    Thanks,

    yp

  • May 21, 2003 at 9:41 pm

    #458086

    Not the best!

    Declare @d SmallDateTime

    Set @d='31 jan 2003'

    Select DateAdd(ms,-3,DateAdd(m,1,'1 '+Right(Convert(Char(11),@d ,113),9)))

  • May 22, 2003 at 2:11 am

    #458101

    Hi ytao,

    although Len's solution works, if you're just just looking for last day of month an year based on the given date what about

    SELECT day(dateadd(d,-day(@date),dateadd(m,1,@date)))

    Works also for YEAR(...)

    It should be a tick faster, because of less operations

    If you want all in one, take Len's solution

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • May 23, 2003 at 7:30 am

    #458348

    Are you checking for the day (dd) or the whole date (yyyy-mm-dd) in your where clause.

    If you want to use the full date then try

    Declare @date datetime,@monthend DateTime

    Set @date='23 may 2003'

    set @monthend = DateAdd(month,1,convert(varchar(8),@date,120)+'01')

    select cols..

    from table

    where datecol >= @date

    and datecol < @monthend

    This will then give you all the data between the start date (@date) and the last day of that month and will cater for datetime cols with time values.

    Far away is close at hand in the images of elsewhere.
    Anon.

  • May 23, 2003 at 5:39 pm

    #458430

    Hi Frank, Len, and David,

    Thank you very much for the help!! I tried all three, all worked although I was looking for the whole date (yyyy-mm-dd).

    One minor thing: Can you guys recommend any good references (web-sites or books) related to date functions and date conversion. I really want to understand how the conversion works.

    Thanks again and have a good weekend!

    yp

  • May 26, 2003 at 1:09 am

    #458467

    Hi yp,

    quote:

    One minor thing: Can you guys recommend any good references (web-sites or books) related to date functions and date conversion. I really want to understand how the conversion works.

    Always a good starting point is BOL, Knowledge base, or MSDN. They are full of useful information when you know where to look at.

    I know in VBA finding the whole date is absolutely simple utilizing the DateSerial function, but I can't find my example. If I do, I'll post it here. Maybe this will make a different point of view

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • June 1, 2003 at 7:17 pm

    #459292

    Hi Frank,

    Thank you very much for your reply! These web sites are very helpful.

    yp

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply