Last Day of a month query

[eo]

Hi,

I am trying to create an insert statement to populate the date dimension. One of the columns relates to whether the date falls on a calendar month end (another on a quarter end, and another on a year end). I have tried all sorts of statements to try and work out if a date falls on a calendar month, quarter and year end, but no luck so far.

Does anyone have any ideas?

EO
Hertfordshire, England

 

[eo]

Answewred my own question, and in case anyone else wondered:

case when MONTH (DATEADD(d, 1, [Date])) > MONTH([Date]) then 'Y' else 'N' end

EO
Hertfordshire, England

 

[jbenson001]

Last day of month:

SELECT DATEADD(month,DATEDIFF(month, 0,GETDATE())+1,0) -1

Last day of year:
(This is overkill since the last day of any year is 12/31)

SELECT DATEADD(year,Datediff(year ,0,getdate())+1,0)-1

Not sure how to do quater end since that can vary on what a quarter is to your business. Some business use a fiscal quarter not a calendar quarter.

 

[simian336]

You might also consider a calendar table.

http://sqlserver2000.databases.aspf...nsider-using-an-auxiliary-calendar-table.html

Simi

 

[markros]

See also

http://wiki.lessthandot.com/index.php/How_to_find_the_first_and_last_days_in_years,_months_etc

PluralSight Learning Library

 

[kss444]

Try this.

IF YEAR(CURRENT_TIMESTAMP) <> YEAR(DATEADD(day, 1, CURRENT_TIMESTAMP))
PRINT 'Today is the last day of the year.'
ELSE
IF MONTH(CURRENT_TIMESTAMP) <> MONTH(DATEADD(day, 1, CURRENT_TIMESTAMP))
PRINT 'Today is the last day of the month but not the last day of the year.'
ELSE
PRINT 'Today is not the last day of the month.';
GO

Ordinary Programmer