Start date of the current month

Share your SQL code snippets for SQL Prompt!

Moderators: David Atkinson, Anu Deshpande, Luke Jefferson, Paul Stephenson

Start date of the current month

Postby Brian Donahue » Tue Mar 18, 2008 10:32 am

Here is a bit of code I have grown tired of typing over and over again. It returns a string describing the date for the start of the current month, for instance '20080301'.
Code: Select all
(SELECT CAST(DATEPART(yyyy,GETDATE())AS NVARCHAR)
+(SELECT CASE WHEN LEN(
DATEPART(mm,GETDATE()))=1
THEN
'0'
ELSE
''
END
+CAST(DATEPART(mm,GETDATE()) AS NVARCHAR(2)))
+'01')
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

First day of month

Postby iceburg » Tue May 06, 2008 3:59 pm

How about this?

select convert(nvarchar(8),dateadd(mm, datediff(mm, 0, getdate()), 0), 112) -- first day of month
iceburg
 
Posts: 5
Joined: Tue May 06, 2008 3:03 pm

Postby Brian Donahue » Tue May 06, 2008 11:13 pm

Thanks, this is much more succinct than the original!
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

A Lookup table might be of interest as well

Postby EdCarden » Thu Mar 01, 2012 6:49 pm

Brian

If you're finding that you need to get the first of the month often then you could look at incorporating a Lookup table into your coide that consist of rows for each date that falls within a range that would cover the dates you'd expect to use for the next 5 yeras or so. The table would include the Date, the first day of that month, the last day of that month as well as other Date related data that is often obtained using one of the t-sql date functions and or string functions.

I work in a date heavy industry in which our transactional table has hundreads of millions of rows. More then %50 of the queries involved need to get the first day of whatever month/Year the date on the transaction falls within. Instead of using some extensive combination of string and date functions I join to a date lookup table on the date and return from it that First day of the month value. Its very handy to have.
EdCarden
 
Posts: 90
Joined: Tue Nov 25, 2008 6:26 pm


Return to SQL Prompt code snippets

Who is online

Users browsing this forum: No registered users and 0 guests