Oct 6

Written by: Peter Henry
Monday, October 06, 2008 7:50 PM

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

 

 

Tags: