March 23, 2009 at 4:41 pm
Problem/Issue: How to show the Month & Year portion only (i.e. MM/YYYY) of a Date in T-SQL
Answer: SELECT Right(Convert(VARCHAR,GetDate(),103),7)
NOTE:This is just 1 of many ways to achieve this goal however I believe there is no other method that involves less steps or function calls but I could be wrong.
I work in an industry with a large number of accounting users and often there is a need to report on data as of some Month/Year date and since T-SQL does not natively provide a way to see a date like this, the above formula uses 2 of T-SQL's native functions to strip out the day of the month portion of the date.
How This works:
Step 1: The Twist-N-Flip
The first part of the formula performs 2 changes or transformations in 1 step. The Convert() function, when passed to it's third argumnet the value 103, twists off the time portion of the date and then it flips the postions of the Month (MM/DD/YY) and the Day-Of-The-Month (MM/DD/YYYY) portions of the date.
The above description assumes that your dates are normally displayed in the format of MM/DD/YYYY. Depending on your region and other seetings you may see dates in T-SQL displayed in a different format.
Step 2: Getting the Right Date
In the second and last portion of the formula the Right() function is used to trim away the first 3 characters which are the Day-Of-The-Month and the seperator character ('/') that comes after this and before the Month portion of the date.
Comments & Notes:
There are several benefits to using this method:
1) Minimal use of String functions which can be a performance drag at times
2) Using the Convert() function, passing 103 as the third argument, ensures you get a date string that is the same number of charcters regardless of the date. This means using the Right() function always works regardless of whether the Month or Day-Of-The-Month are 1 or 2 digits in length; eliminating the need for using Len() before extracting the Right-most characters.
3) No third party objects like Lookup tables are needed.
4) Portability - As far as I know this works in every version of SQL Server from 7 to 2008.
5) Readability - Because it's short and simple it's easy to read/follow.
[font="Arial Black"]Whats Your Favorite Date/Time Trick/Tip in T-SQL?[/font]
--------------------------------------------------------------------------------------
Hopefully this post will spawn some great tips/tricks on working with dates in T-SQL. Anyone whos worked with T-SQL and any procedural language like VB or C knows that in T-SQl sometimes you have to be creative to change how a date looks or works in your SQL Code. Below is one such trick I stumbled across and was excited because I thought I had come up with something new in T-SQL when in truth nothing is really new in T-SQL as much as long lost or new-again.
Please contribute to this thread any and all Date And/Or Time Tricks in T-SQL you've found handy and feel others could benefit from.
Kindest Regards,
Just say No to Facebook!March 23, 2009 at 4:45 pm
Why did you post a duplicate thread? That just makes people mad.
Duplicate
http://www.sqlservercentral.com/Forums/Topic681945-338-1.aspx
And I see now that you deleted the original post after people had commented, making it hard to know what the thread was about. That really makes people mad.
March 23, 2009 at 4:51 pm
Below was posted in response to a copy of this posting that was not supposed to have gone up yet:
From Bruce W Cassidy:
Okay, some thoughts:
The first is, do you really need to be formatting dates at the SQL Server level? A lot of the time, this is best left to the application or reporting tool. Just store dates as dates in SQL Server, and don't worry about formatting there if you can avoid it at all.
In the cases where you can't avoid it, have you considered creating a Calendar table? This can contain all of those nasties like Julianised dates, financial periods, various date formats and so on. You can set them up the once, and know that you will always get them right. Also, it encourages using sets of data.
This is an excellent question. In my particular situation we do have the ability to do this date formatting at the client within the application used to display/report the data like Crystal Reports. We have done this with some items however the benefit to doing it within T-SQL is that we are better able to enforce consistency.
There are several users with the ability to create their own reports in various formats such as Crystal, Excel and so on. By giving them a pre-defined view of data in which the date is already coming over as MM/YYYY we know ever user is going to see/work with the date in the same way.
As for a calendar table, we do have a lookup table that contains 1 row for every unique date (MM/DD/YYYY) that conatins a wide range of information from the first day of the MM/YYYY the date falls within to the last day of that month. The goal was to minimize the need for additional objects so as to make the objct (a view in this case) more portable.
Thanks for posting!
Kindest Regards,
Just say No to Facebook!March 23, 2009 at 4:56 pm
Another great question posted by Michael Valentine Jones
Why not at least produce the year and month in a sortable form?
select YearMonth = convert(varchar(7),getdate(),121)
Results:
YearMonth
---------
2009-03
I have links to a lot of info about working with Date/Time here, so if anyone is interested...
Date/Time Info and Script Links
The choice to go with MM/YYYY vereses YYYY-MM was one of choice (by those among our users requesting custom reports and who are the decision makers). had I gone with YYYY-MM I would then have to perform another conversion on the object displaying the date.
Thanks for posting the link to additional Date tricks. When I was trying to determine how best to do this I couldn't find any useful info on the net searching in Google. This was because I was not able to word my question the way that would be needed to return hist specific to this date format need. Of course once I had the answer I was able to find hundreads of hits by searching on the answer itself.
Kindest Regards,
Just say No to Facebook!March 23, 2009 at 5:41 pm
The first is, do you really need to be formatting dates at the SQL Server level? A lot of the time, this is best left to the application or reporting tool. Just store dates as dates in SQL Server, and don't worry about formatting there if you can avoid it at all.
There are a huge number of instances where you would want to format the date within sql or get its various elements.
Generally these will be to to with grouping, pivoting or comparison of specific date elements.
For these reasons it is good to know the best ways to get the various date aspects.
T-SQL does of course offer some shortcuts to using CONVERT and substrings such as YEAR(), MONTH(), DAY() which add to readability though I have not run extensive time trials on their performance. I am assuming though that there is not an easily noticable difference between MONTH(mydate) amd DATEPART(mm,mydate).
[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]
March 23, 2009 at 5:59 pm
Rob Fisk (3/23/2009)
There are a huge number of instances where you would want to format the date within sql or get its various elements.
[font="Verdana"]Agreed. But there are also a huge number of instances where you don't. So I like to check first. And the Calendar table I recommend works well for doing things like date range grouping.
It does get a bit tricky when you are working across multiple time zones and with things like regional holidays though. I'm not sure I know an easy solution to those, but at least the Calendar table is a good start.
[/font]
March 24, 2009 at 1:44 am
Converting to varchar and back is a poor way of getting the start of month. It's much slower than using date functions.
select dateadd(mm, datediff(mm,0, getdate()),0)
Then the formatting to not show day and hour can be done on the front end.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 24, 2009 at 8:24 am
GilaMonster (3/24/2009)
Converting to varchar and back is a poor way of getting the start of month. It's much slower than using date functions.
select dateadd(mm, datediff(mm,0, getdate()),0)Then the formatting to not show day and hour can be done on the front end.
Gail,
Where did you see this formula listed ofr getting the Start of the month?
Kindest Regards,
Just say No to Facebook!March 24, 2009 at 8:40 am
YSLGuru (3/24/2009)
Where did you see this formula listed ofr getting the Start of the month?
I don't recall seeing it anywhere. The version to trim the time off a date is fairly common and I experimented with it, seeing what else it can do with different time intervals.
I blogged about them (more so I could find the formula if I couldn't remember), but that's not my source obviously.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 24, 2009 at 1:08 pm
GilaMonster (3/24/2009)
YSLGuru (3/24/2009)
Where did you see this formula listed ofr getting the Start of the month?I don't recall seeing it anywhere. The version to trim the time off a date is fairly common and I experimented with it, seeing what else it can do with different time intervals.
I blogged about them (more so I could find the formula if I couldn't remember), but that's not my source obviously.
[font="Verdana"]It may have been your blog where I read about it then. It's a handy trick to know, although I mostly still use it in the "stripping off time" form.[/font]
March 24, 2009 at 3:02 pm
I am reposting various methods for start of time periods that I originally posted on the links below.
Start of Time Period Functions
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755
Function F_START_OF_WEEK to find the first day of the week at this link:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307
/*
Script to demo Start of Time period methods in-line for various types of time periods.
All methods will run with no error for any valid datetime value
*/
set nocount on
declare @day table (DT datetime not null primary key clustered)
-- Load Test Dates
insert into @day (DT)
select DT = getdate()
union all
select '2014-05-24 16:56:27.667'
union all
select '17530101 00:00:00.000'
union all
select '17591231 23:59:59.997'
union all
select '17600101 00:00:00.000'
union all
select '17991231 23:59:59.997'
union all
select '18000101 00:00:00.000'
union all
select '99991231 23:59:59.997'
order by 1
select Test_Dates = DT from @day order by 1
selectSTART_OF_CENTURY =
case
when a.DT < '18000101'
then convert(datetime,null)
else dateadd(yy,(datediff(yy,'18000101',a.DT)/100)*100,'18000101')
end
from @day a
selectSTART_OF_DECADE =
case
when a.DT < '17600101'
then convert(datetime,null)
else dateadd(yy,(datediff(yy,'17600101',a.DT)/10)*10,'17600101')
end
from @day a
select START_OF_YEAR = dateadd(yy,datediff(yy,0,a.DT),0) from @day a
select START_OF_QUARTER = dateadd(qq,datediff(qq,0,a.DT),0) from @day a
select START_OF_MONTH = dateadd(mm,datediff(mm,0,a.DT),0) from @day a
select START_OF_DAY = dateadd(dd,datediff(dd,0,a.DT),0) from @day a
select START_OF_HOUR = dateadd(hh,datediff(hh,0,a.DT),0) from @day a
select START_OF_30_MIN = dateadd(mi,(datepart(mi,a.DT)/30)*30,dateadd(hh,datediff(hh,0,a.DT),0)) from @day a
select START_OF_20_MIN = dateadd(mi,(datepart(mi,a.DT)/20)*20,dateadd(hh,datediff(hh,0,a.DT),0)) from @day a
select START_OF_15_MIN = dateadd(mi,(datepart(mi,a.DT)/15)*15,dateadd(hh,datediff(hh,0,a.DT),0)) from @day a
select START_OF_10_MIN = dateadd(mi,(datepart(mi,a.DT)/10)*10,dateadd(hh,datediff(hh,0,a.DT),0)) from @day a
select START_OF_05_MIN = dateadd(mi,(datepart(mi,a.DT)/5)*5,dateadd(hh,datediff(hh,0,a.DT),0)) from @day a
select START_OF_MINUTE = dateadd(ms,-(datepart(ss,a.DT)*1000)-datepart(ms,a.DT),a.DT) from @day a
select START_OF_SECOND = dateadd(ms,-datepart(ms,a.DT),a.DT) from @day a
Results:
[font="Courier New"]
Test_Dates
------------------------------------------------------
1753-01-01 00:00:00.000
1759-12-31 23:59:59.997
1760-01-01 00:00:00.000
1799-12-31 23:59:59.997
1800-01-01 00:00:00.000
2009-03-24 16:55:54.903
2014-05-24 16:56:27.667
9999-12-31 23:59:59.997
START_OF_CENTURY
------------------------------------------------------
NULL
NULL
NULL
NULL
1800-01-01 00:00:00.000
2000-01-01 00:00:00.000
2000-01-01 00:00:00.000
9900-01-01 00:00:00.000
START_OF_DECADE
------------------------------------------------------
NULL
NULL
1760-01-01 00:00:00.000
1790-01-01 00:00:00.000
1800-01-01 00:00:00.000
2000-01-01 00:00:00.000
2010-01-01 00:00:00.000
9990-01-01 00:00:00.000
START_OF_YEAR
------------------------------------------------------
1753-01-01 00:00:00.000
1759-01-01 00:00:00.000
1760-01-01 00:00:00.000
1799-01-01 00:00:00.000
1800-01-01 00:00:00.000
2009-01-01 00:00:00.000
2014-01-01 00:00:00.000
9999-01-01 00:00:00.000
START_OF_QUARTER
------------------------------------------------------
1753-01-01 00:00:00.000
1759-10-01 00:00:00.000
1760-01-01 00:00:00.000
1799-10-01 00:00:00.000
1800-01-01 00:00:00.000
2009-01-01 00:00:00.000
2014-04-01 00:00:00.000
9999-10-01 00:00:00.000
START_OF_MONTH
------------------------------------------------------
1753-01-01 00:00:00.000
1759-12-01 00:00:00.000
1760-01-01 00:00:00.000
1799-12-01 00:00:00.000
1800-01-01 00:00:00.000
2009-03-01 00:00:00.000
2014-05-01 00:00:00.000
9999-12-01 00:00:00.000
START_OF_DAY
------------------------------------------------------
1753-01-01 00:00:00.000
1759-12-31 00:00:00.000
1760-01-01 00:00:00.000
1799-12-31 00:00:00.000
1800-01-01 00:00:00.000
2009-03-24 00:00:00.000
2014-05-24 00:00:00.000
9999-12-31 00:00:00.000
START_OF_HOUR
------------------------------------------------------
1753-01-01 00:00:00.000
1759-12-31 23:00:00.000
1760-01-01 00:00:00.000
1799-12-31 23:00:00.000
1800-01-01 00:00:00.000
2009-03-24 16:00:00.000
2014-05-24 16:00:00.000
9999-12-31 23:00:00.000
START_OF_30_MIN
------------------------------------------------------
1753-01-01 00:00:00.000
1759-12-31 23:30:00.000
1760-01-01 00:00:00.000
1799-12-31 23:30:00.000
1800-01-01 00:00:00.000
2009-03-24 16:30:00.000
2014-05-24 16:30:00.000
9999-12-31 23:30:00.000
START_OF_20_MIN
------------------------------------------------------
1753-01-01 00:00:00.000
1759-12-31 23:40:00.000
1760-01-01 00:00:00.000
1799-12-31 23:40:00.000
1800-01-01 00:00:00.000
2009-03-24 16:40:00.000
2014-05-24 16:40:00.000
9999-12-31 23:40:00.000
START_OF_15_MIN
------------------------------------------------------
1753-01-01 00:00:00.000
1759-12-31 23:45:00.000
1760-01-01 00:00:00.000
1799-12-31 23:45:00.000
1800-01-01 00:00:00.000
2009-03-24 16:45:00.000
2014-05-24 16:45:00.000
9999-12-31 23:45:00.000
START_OF_10_MIN
------------------------------------------------------
1753-01-01 00:00:00.000
1759-12-31 23:50:00.000
1760-01-01 00:00:00.000
1799-12-31 23:50:00.000
1800-01-01 00:00:00.000
2009-03-24 16:50:00.000
2014-05-24 16:50:00.000
9999-12-31 23:50:00.000
START_OF_05_MIN
------------------------------------------------------
1753-01-01 00:00:00.000
1759-12-31 23:55:00.000
1760-01-01 00:00:00.000
1799-12-31 23:55:00.000
1800-01-01 00:00:00.000
2009-03-24 16:55:00.000
2014-05-24 16:55:00.000
9999-12-31 23:55:00.000
START_OF_MINUTE
------------------------------------------------------
1753-01-01 00:00:00.000
1759-12-31 23:59:00.000
1760-01-01 00:00:00.000
1799-12-31 23:59:00.000
1800-01-01 00:00:00.000
2009-03-24 16:55:00.000
2014-05-24 16:56:00.000
9999-12-31 23:59:00.000
START_OF_SECOND
------------------------------------------------------
1753-01-01 00:00:00.000
1759-12-31 23:59:59.000
1760-01-01 00:00:00.000
1799-12-31 23:59:59.000
1800-01-01 00:00:00.000
2009-03-24 16:55:54.000
2014-05-24 16:56:27.000
9999-12-31 23:59:59.000
[/font]
March 24, 2009 at 4:50 pm
Michael Valentine Jones (3/24/2009)
I am reposting various methods for start of time periods that I originally posted on the links below.
[font="Verdana"]Thanks so much, Michael. Very fortuitous timing. I am generating the contents of a time dimension for our data warehouse, which has a whole bunch of pre-defined time bands for convenient grouping. Your example code made it very easy![/font]
March 24, 2009 at 5:19 pm
If you have a need for it, the function on this link generates a very complete (around 60 columns) result set for loading a Date Dimension table.
Date Table Function F_TABLE_DATE
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519
Other good datetime stuff here:
Other Date/Time Info and Script Links:
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply