| All blog entries are the opinions of the author and do not necessarily reflect the opinions of their employer. All the code presented is for explanation and demonstration purposes only. Any damages incurred to your site and/or data are not the responsibility of the author. Every effort is taken to ensure the code properly compiles, however sometimes there are some hiccups and you might be required to do your own debugging. |
|
|
T-SQL Date Manipulation
Oct
6
Written by:
Monday, October 06, 2008 7:50 PM
Have you ever wanted to get the Date part of a DateTime field in SQL Server? What about the Time part? What about the different formats available to you? You might know you need to use Cast/Convert, DateAdd, DatePart or DateDiff but who can remember their parameters? What about the order of the parameters? Oh you can? Good for you, here's your hero biscuit, for the rest of us, there's this link. :>
I'm creating the website for the study group I'm participating in, I've found myself writting a more than a a few procs dealing with dates and time manipulations. Here is some key information about manipulating these.
CAST and CONVERT are your friends in this situation. Yes, you are going to have to convert your DateTime milliseconds value to a string. Get over it. :> It's just something you have to get over and do. Here are some useful examples and output you can expect.
Date and Time Display Manipulation
|
SQL
|
Example Result
|
| Defaults |
|
|
CONVERT
(CHAR(19), SessionAttendance.Arrive) |
May 26 2008 6:00PM |
| Dates |
|
| CONVERT(CHAR(12), Sessions.SessionDate, 105) |
26-05-2008 |
| CONVERT(CHAR(12), Sessions.SessionDate, 106) |
26 May 2008 |
| CONVERT(CHAR(12), Sessions.SessionDate, 107) |
May 26, 2008 |
| CONVERT(CHAR(12), Sessions.SessionDate, 110) |
05-26-2008 |
| Times |
|
| CONVERT(CHAR(5), SessionAttendance.Arrive, 8) |
18:00 |
| CONVERT(CHAR(5), SessionAttendance.Arrive, 14) |
18:00 |
|
CONVERT
(CHAR(30), SessionAttendance.Arrive) |
May 26 2008 6:00PM |
|
CONVERT
(CHAR(19), SessionAttendance.Arrive) |
May 26 2008 6:00PM |
Now you know how to display dates and times differently, what about figuring out differences in them? By using the DateAdd of course.
Other Useful Date And Time Manipulation
|
SQL
|
Description
|
|
DATEDIFF
(mi, CONVERT(char(8), '18:00', 14), CONVERT(CHAR(8), SessionAttendance.Arrive, 14)) <= 15 |
Comparing a made up time to a value from the db to see if they are more/less than 15 minutes apart. |
|
DatePart
(n, SessionAttendance.Arrive) |
Gets the Minutes of the time part, yes, "n" is for minutes, don't believe me? I don't blame you, I couldn't believe it neither. |
|
DatePart
(m, SessionAttendance.Arrive) |
"m" is already taken for the Month. |
So there you have it, a small sample of the date and time manipulation functions from SQL Server.
References:
MSDN Cast and Convert link and Style table
MSDN DateAdd link
MSDN DateDiff function
MSDN DatePart function
2 comment(s) so far...
Re: T-SQL Date Manipulation
I just heard from DM that CAST is the ANSI SQL Standard way to change data types where as the CONVERT is T-SQL specific syntax. Thank you DM! :>
By phenry on
Monday, October 13, 2008 8:13 PM
|
Re: T-SQL Date Manipulation
I just ready on SQLServerCentral (http://www.sqlservercentral.com/articles/Performance+Tuning/67427/ goto bullet 12) that CAST is the ANSI-92 standard (CONVERT is SQL Server specific). Therefore if writting generic queries to be used elsewhere, CAST is your function. Except when trying to format the DATETIME in a specific style, then you have to use CONVERT.
By phenry on
Monday, August 10, 2009 8:46 AM
|
|
|
|