SQL Server Dates - finding the last day of the month.

[sivi]

Please could someone help me:

I need to find the last day of any month depending on month stored in the field.

For example the date value stored on the table is:

15032003

I need to find the last day of the month that is '31' for March using the month value '03' in this example. I need this value to update another field.

Many thanks
Sivi

 

[skuhlman]

If your date is already in datetime format, this will work:

select dateadd(d,-day(MyDate),dateadd(m,1,MyDate)) as lastdayofmonth from MyTable

If it's in character or numeric format you'll need to use the convert or cast to change it to datetime.

 

[AWithers]

This may not help very much but below is how I have done something similar in VB before...hopefully you will get the idea

using the data you have you will need to extract the month portion as a number, add 1...construct the datevalue of the first of this month then takeaway one day then maybe covert back to a string


Andy

currentmonthstart = "1-" + Format(Date, "mmm&quot + "-" + Right((Str(Year(Date))), 2)
previousmonthenddate = DateValue(currentmonthstart) - 1

 

[osjohnm]

Hi

If you want the year, month and day then try this:

SELECT DATEADD(dd,-1,DATEADD(mm,1,DATEADD(d,-DATEPART(dd,GETDATE()) + 1, CONVERT(VARCHAR,GETDATE(),101))))

If you want the just the day such as '31' then try this:

SELECT DATEPART(d, DATEADD(dd,-1,DATEADD(mm,1,DATEADD(d,-DATEPART(dd,GETDATE()) + 1, CONVERT(VARCHAR,GETDATE(),101)))))

You must just replace the getdate() with your date eg.

SELECT DATEPART(d, DATEADD(dd,-1,DATEADD(mm,1,DATEADD(d,-DATEPART(dd,'2003-03-15') + 1, CONVERT(VARCHAR,'2003-03-15',101)))))

Hope this helps

John