Search
Tuesday, November 20, 2018 ..:: Home ::.. Register  Login
   Calendar  
     
  
   Search  
     
  
   Blogroll  
     
  
   Disclosure  
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.
     
  
   TechTidBits (Blog)  

T-SQL Date Manipulation

Oct 6

Written by:
Monday, October 06, 2008 7:50 PM  RssIcon

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

 

 

Tags:
Categories:
Location: Blogs Parent Separator TechTidBits

3 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
Gravatar

Re: T-SQL Date Manipulation

Hi Jose, thanks for leaving a question. The trick here is to understand the getdate() and the date you see in the tool you're using aren't the same. Think of the one you see as being in front of sunglasses. What I mean is, in behind the scenes, MS SQL Server stores, thinks of, sees dates as a big integer, milliseconds from epoch (Jan 1, 1970). But you don't see that, you see a real live date that was converted for you by SQL Server (so you might see this, 03-17-2012 13:24.26 or I might see this 17/03/12 1:24.26 PM). It doesn't really matter how YOU see it, but how SQL Server sees it (I'm not trying to diminish YOU, DOH!, no no no, just that you should care about sql server when you're comparing dates).

Ok, ALL that to say, you should be able to just compare the dates using the or those operators. Try that out. Good luck and thanks for leaving a comment.

By Peter Henry on   Saturday, March 17, 2012 9:51 AM

Your name:
Gravatar Preview
Your email:
(Optional) Email used only to show Gravatar.
Your website:
Title:
Comment:
Add Comment   Cancel 
     
  
Copyright 1999-2012 by PCHenry.com   Terms Of Use  Privacy Statement