Red Gate forums :: View topic - Start date of the current month
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation

SQL Prompt code snippets forum

Start date of the current month

Search in SQL Prompt code snippets forum
Post new topic   Reply to topic
Jump to:  
Author Message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6669

PostPosted: Tue Mar 18, 2008 10:32 am    Post subject: Start date of the current month Reply with quote

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 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')
Back to top
View user's profile Send private message
iceburg



Joined: 06 May 2008
Posts: 5

PostPosted: Tue May 06, 2008 3:59 pm    Post subject: First day of month Reply with quote

How about this?

select convert(nvarchar(8),dateadd(mm, datediff(mm, 0, getdate()), 0), 112) -- first day of month
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6669

PostPosted: Tue May 06, 2008 11:13 pm    Post subject: Reply with quote

Thanks, this is much more succinct than the original!
Back to top
View user's profile Send private message
EdCarden



Joined: 25 Nov 2008
Posts: 90

PostPosted: Thu Mar 01, 2012 6:49 pm    Post subject: A Lookup table might be of interest as well Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group