How to find the last of a date field

Question

Post reply

How to find the last of a date field

ytao

Newbie

Points: 7
More actions
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

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

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

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]

ytao Newbie Points: 7 More actions · Answer 2

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

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 3

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)))

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

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]

David Burrows SSC Guru Points: 65144 More actions · Answer 5

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.

ytao Newbie Points: 7 More actions · Answer 6

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

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]

ytao Newbie Points: 7 More actions · Answer 8

Hi Frank,

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

yp