Brettb.Com
  HOME | ABOUT ME | BIOTECHNOLOGY | ARTICLES | GALLERY | CONTACT
Search: Go
TECHNICAL ARTICLES
 ASP
 ASP.NET
 JavaScript
 Transact SQL
 Other Articles
 Software Reviews

PHOTO GALLERIES
 Canon EOS 300D Samples
 Akihabara Maids!
 More Galleries...

TRAVEL LOG
 2009: China
 2008: Tokyo
 2007: Tokyo
 2006: Hong Kong
 2005: New York City

MORE STUFF
 Search Engine Optimisation
 Build an ASP Search Engine
 My Tropical Fishtank
 Autoglass
 SQL Month Name
 SQL Get Date Today
 SQL Year Month
 Other New Stuff...

POPULAR STUFF
 Regular Expressions
 Index Server & ASP
 JavaScript Ad Rotator

Home > Articles > Transact SQL Programming Articles

SQL Server Help: Using Microsoft's Transact SQL DatePart function

Getting the current date (and time) in SQL Server's SQL is possible by using the GetDate function. This can be used to show the current date:

SELECT GetDate() AS 'CurrentDate'

The GetDate function can also be used to assign the current date to a datetime variable, as shown in the following example:

DECLARE @TodaysDate DATETIME
SELECT @TodaysDate = GetDate()
SELECT 'Today''s date is ' + convert(varchar(20), @TodaysDate)

DatePart

The GetDate function returns the current entire date and time. To just retrieve the year, month, week or time period it is possible to use the DatePart function, passing in the date variable as a parameter (the date can also come from a database datetime or smalldatetime field.

The DatePart function takes two arguments, a date part, plus a date variable (which can be either a datetime or smalldatetime). The first argument, the date part, can be one of the following abbreviations:

yy, yyyy
Year
qq, q
Quarter
mm, m
Month
dy, y
DayOfYear
dd, d
Day
wk, ww
Week
hh
Hour
mi, n
Minute
ss
Second
ms
Millisecond

The date part does not need to be enclosed in quotes.

In the example below, the SQL statement is used to find orders where the ShippedDate year was 2008:

SELECT * FROM Orders WHERE DATEPART(yy, ShippedDate) = 2008

Note that Transact SQL has a few shortcuts that can be used instead of DatePart:

  • Day(@DateVariable) is the equivalent of DatePart(dd, @DateVariable)
  • Month(@DateVariable) is the equivalent of DatePart(mm, @DateVariable)
  • Year(@DateVariable) is the equivalent of DatePart(yy, @DateVariable)

Useful Links

  • The SQL Documentation Tool automatically builds technical documentation for Microsoft SQL Server databases, saving you time and money. A trial version is available for download.

  Site Map | Privacy Policy

All content is 1995 - 2012