SqlShare.com Logo
 
Skip Navigation Links
Home
Video List
Classes
About Us
Login / Register
Subscribe RSS Feed 

Download a free trial of SQL Backup now

Using DatePart and DateName

Ever want to get the month out of a date as a number, or as literal text? Many people will do it by parsing the date as a string, but we've got some built in functions that will do it cleanly and consistently.

Duration:
2 mins 36 secs
Skill Level:
100
Rating:
4.36 out of 5
Publish Date:
March 10, 2010
Using DatePart and DateName Watch Video Now  Watch it later!
Bookmark and Share
 
1=Poor, 3=Good, 5=Excellent

About the Author

Image of Andy Warren
Andy Warren is a software trainer focusing on SQL Server, a member of the PASS Board of Directors, and a principal in this site - SQLShare.com.

References



Comments
vijayan on 3/11/2010
nice

vijayan on 3/11/2010
nice

Al on 3/11/2010
Excellent tutorial!!

Arun on 3/11/2010
Very good

Steve Harris on 3/11/2010
perhaps talk about other date functions like using CONVERT

Richard Rostant on 3/11/2010
Great vid

Kristeen Sellers on 3/11/2010
This is good general information, if the date you are recieving is in a format that is valid for the Datepart function.. However, I have a specific case, where I receive mmddyy, no punctuation. The following code: SELECT datepart(yyyy, '030510') as WhatYear returns 2003 as the year, instead of 2010. In this case, I must parse the date.

Kenneth Wymore on 3/11/2010
A good introductory video. It would be nice to see a sample where the data format for a date column has changed in a table so users can see exactly what you mean when you say that "parsing out a year or month or quarter can be dangerous versus using the datepart function".

Jeff on 3/11/2010
Awesome!

Gary Fairchild on 3/11/2010
why use getutcdate and not getdate?

Sara Karasik on 3/11/2010
Really well presented, but way, way to simple and beginner level.

WChaster on 3/11/2010
I know I need this one

Denise Tsubota on 3/11/2010
I knew most of this but found the part about passing in a parameter value very helpful - I didn't know that piece. thank you very much - Denise

codputer on 3/11/2010
I always use the date formate dd-MMM-yyyy, as that always converts correctly. I created a function that always returns this date format, and I never have to worry about the culture of the box

Kevin on 3/11/2010
Excellent video. Thanks.

Tim Harms on 3/11/2010
Useful information but it didn't blow my mind...gave it a 3.

Lance on 3/11/2010
Keep these things coming!!!

Sherri McDonald on 3/11/2010
I am new to sql right now, so this is beyond my skill set.

Munna Bhai on 3/11/2010
This is good stuff and very helpful. Thank you for sharing and keep up the good work.

142CD85A2D on 3/11/2010
Very helpful. I would be helpful if the examples could be downloaded.

neeta on 3/11/2010
It was a good video. I have a very slow connection, but it helped having the code written out, highlighted and executed. Thank you.

Diana Dee on 3/13/2010
When the results pane will obscure the code in the query window, please scroll down so that the code remains in view as you get a results pane. This would help those of us who are a little slow or who cannot remember what we saw for a very brief time. Thanks. Andy, you do an outstanding job on these!! Diana Dee

Andy Jarman on 3/14/2010
Great series of videos - really appreciate the work you put into them. SO I don't want to appear 'picky', but your "prove it returns a string" example - uses DATEPART(), rather than DATENAME() - although the end result is the same ;-) Additionally, probably too complex, but your last example could be achieved using Dynamic SQL..... Like I said - love your work. Andy

luther smith on 3/15/2010
Simple and clear.

John on 3/18/2010
I believe you have a mistake/oversight - you forgot to change "datepart" to "datename" in the "prove it returns a string" block. What really matters is the datatype of your variable as these have identical results: declare @YearInt int declare @YearStr varchar(20) select @YearInt = datename(yyyy,getutcdate()) select @YearStr = datename(yyyy,getutcdate()) select @YearInt, @YearStr select @YearInt +10, @YearStr + 10 select @YearInt = datepart(yyyy,getutcdate()) select @YearStr = datepart(yyyy,getutcdate()) select @YearInt, @YearStr select @YearInt +10, @YearStr + 10

ML Stephens on 3/30/2010
getting the datepart back as on int---I've always done gross manipulated because I was changing it to a varchar

Sam Schafer on 6/3/2010
Well done. Very good point about maintaining integrity of values if inputted format of date data changes.



Must Be Logged In
 

How Do I Become a Video Author? |  Newsletter History

Copyright © Fourdeuce, Inc., 2005-2009. All Rights Reserved | Privacy Policy | Terms & Conditions