Page 1 of 1

Working With Date Values in SQL Server

#1 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1633
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Post icon  Posted 09 February 2008 - 11:13 PM

Welcome to this tutorial on Working With Date Values in SQL Server. In this tutorial we will take a look at the different ways we can manipulate and display date and time values with Transact-SQL. We will look at the different intrinsic functions in Transact-SQL, the language of SQL Server, that can be used to accomplish this.

Depending on your environment, your needs, and/or the audience of your application, the format for displaying date and time might vary. Internationally we have many difference ways to represent a given date and/or time. Here are a few examples of different ways we might display the date February 09, 2008 with or without a time of 10:31 PM.

  • 2008-02-09 10:31 PM
  • 2008-02-09 22:31
  • 09-02-2008
  • 09 February 2008
  • Feb 09 2008 10:13 PM
  • February 09, 2008



Let's review the SQL Server functions that can be used to display these date formats, starting with the CONVERT function. The CONVERT function is provided to help with converting a DATETIME or SMALLDATETIME variables, or any other string that holds a valid date, into different date/time display formats. The CONVERT function is called using the following syntax:


Quote

CONVERT ( data_type [ ( length) ] , expression [ , style ] )



Where data_type [(length)] is the target data type format and length, expression is any valid Microsoft expression that represents the date/time you want to display, and style specifies the output format for the data/time.

Using the CONVERT function, with different styles, allows you to display date and time data in many different formats. Let's look at what I am taking about. The easiest way to demonstrate how to use the CONVERT function is to review some Transact-SQL code that displays the current time in a few different display formats. The following script uses only the CONVERT function to display the different formats. We will use the GETDATE function to get us the current date/time:


PRINT '1) Date/time in format MON DD YYYY HH:MI AM (OR PM): ' + CONVERT(CHAR(19),GETDATE())  
PRINT '2) Date/time in format MM-DD-YY: ' + CONVERT(CHAR(8),GETDATE(),10)  
PRINT '3) Date/time in format MM-DD-YYYY: ' + CONVERT(CHAR(10),GETDATE(),110) 
PRINT '4) Date/time in format DD MON YYYY: ' + CONVERT(CHAR(11),GETDATE(),106)
PRINT '5) Date/time in format DD MON YY: ' + CONVERT(CHAR(9),GETDATE(),6) 
PRINT '6) Date/time in format DD MON YYYY HH:MM:SS:MMM(24H): ' + CONVERT(CHAR(24),GETDATE(),113)



This script will produce this output:

  • Date/time in format MON DD YYYY HH:MIAM (OR PM): Feb 09 2008 10:31 PM
  • Date/time in format MM-DD-YY: 02-09-08
  • Date/time in format MM-DD-YYYY: 02-09-2008
  • Date/time in format DD MON YYYY: 09 Feb 2008
  • Date/time in format DD MON YY: 09 Feb 08
  • Date/time in format DD MON YYYY HH:MM:SS:MMM(24H): 09 Feb 2008 10:31:39:567



As you can see, this script displays the current date in many different formats. Some formats have two digit years, while others have four digit years. Some displays have 24 hour or AM/PM time formats. Still others have the month displayed as a numeric value, while others have a month abbreviation. You will also notice that in order to accomplish this we need to convert the date into the CHAR data type with a specific length, so you need to know the length the result will be in when you convert it.

As you can see, the CONVERT function allows you to output the date & time in many different formats and styles, but it isn't an all inclusive function for all styles and formats. Sometimes you will need to use other Transact-SQL functions as well, to get the display format you desire. We will be looking at these intrinsic (built-in) function available to you in Microsoft SQL Server.

The first date time function built into SQL Server that we will be looking at is the DATEPART function. This function will take a date/time expression and return a single part of the date, such as hour, minute, month, day, etc. The syntax for using DATEPART is:


Quote

DATEPART(datepart, date)



Where the datepart is one of the following:

  • Year: yy, yyyy
  • Quarter: qq, q
  • Month: mm, m
  • dayofyear: dy, y
  • Day: dd, d
  • Week: wk, ww
  • Weekday: dw
  • Hour: hh
  • Minute: mi, n
  • Second: ss, s
  • Millisecond: ms.

And date is a valid date expression. This function will return an integer representing the particular date part requested.

Lets take a look at how the CONVERT and/or DATEPART functions can be used to display February 09, 2008 with a time of 10:31 PM to meet all of the display formats I showed at the top of this article.

Some of the formats can be created using the different "style" options on the CONVERT statement. Although a number of these formats above will not only require the CONVERT and/or DATEPART functions, but other Transact-SQL functions like SUBSTRING, RIGHT, and CAST to build the desired display format. To show you how to create each of these display formats I will build a simple script for each of the different formats.

The syntax for SUBSTRING looks like:

Quote

SUBSTRING ( expression ,start , length )


Where expression is the value you want to retrieve part of, start is the integer value to start, and length is how many characters to retrieve. The syntax for RIGHT looks like:

Quote

RIGHT ( character_expression , integer_expression )


Where expression is character or binary data to retrieve from, and integer_expression is an integer value of how many characters to return. The syntax for CAST looks like:

Quote

CAST ( expression AS data_type [ (length ) ])


Where expression is a valid SQL expression, data_type is the data type to cast the expression as.

This first example displays the date in 2008/02/09 10:13PM format. This can be done with the following simple script that uses the CONVERT and SUBSTRING functions. Note this example uses the 111 style format of the CONVERT function:


DECLARE @date DATETIME
SET @date = '2008-02-09 10:31 PM'
SELECT CONVERT(CHAR(11),@date,111) + SUBSTRING(CONVERT(CHAR(19),@date,100),13,19)



As with any language there is always more than 1 way to get the same results. The following example produces the same output as the above script, except this time we use the DATEPART, CAST and RIGHT function together. In this example you will also be introduced to the CASE statement in Transact-SQL:


DECLARE @date DATETIME
SET @date = '2008-02-09 10:31 PM'
SELECT CAST(DATEPART(YYYY,@date) AS CHAR(4)) + '/' 
     + RIGHT(CAST(100+DATEPART(MM,@date) AS CHAR(3)),2) + '/'
     + RIGHT(CAST(100+DATEPART(DD,@date) AS CHAR(3)),2) + ' '
     + CASE WHEN DATEPART(HH,@date) < 13 
            THEN RIGHT(CAST(100+DATEPART(HH,@date) AS CHAR(3)),2) 
            ELSE CAST(DATEPART(HH,@date)-12 AS CHAR(2)) 
            END + ':'
     + RIGHT(CAST(100+DATEPART(MI,@date) AS CHAR(3)),2)     
     + CASE WHEN DATEPART(HH,@date) < 13
            THEN 'AM'
            ELSE 'PM'
            END


In this next example we will display the same date/time value, except with the 24 hour clock (2008-02-09 22:31). We will then use the REPLACE function to replace the dashes, from CONVERT style 120, to forward slashes ("/"):


DECLARE @date DATETIME
SET @date = '2008-02-09 10:31 PM'
SELECT REPLACE(CONVERT(CHAR(16),@date,120),'-','/')


In the next example we will display the date in the format of 09-02-2008 minus the time portion. To accomplish this we will need to truncate the time part of the datetime value by specifying an output data type (CHAR) and a length (10). Doing this will cause the CONVERT function to display only the first 10 characters of the date, using style 105:


DECLARE @date DATETIME
SET @date = '2008-02-09 10:31 PM'
SELECT CONVERT(CHAR(10),@date,105)



In this next example we will be introduced to the DATENAME function to display the date in the format of 09 February 2008. The syntax of DATENAME looks like:

Quote

DATENAME ( datepart ,date )


Where the datepart is one of the following:
  • Year: yy, yyyy
  • Quarter: qq, q
  • Month: mm, m
  • dayofyear: dy, y
  • Day: dd, d
  • Week: wk, ww
  • Weekday: dw
  • Hour: hh
  • Minute: mi, n
  • Second: ss, s
  • Millisecond: ms

and date is a valid Transact-SQL date expression. The "weekday" parm returns the day name, like Sunday, Monday, Tuesday, etc, and the "month" parm returns the month name, like January, February, March, etc. This example uses DATENAME to return the DAY, MONTH and YEAR of the variable @date. The month portion of the date will be returned with the spelled out version of the month, or in this case "February":


DECLARE @date DATETIME
SET @date = '2008-02-09 10:31 PM'
SELECT DATENAME(DAY,@date) + ' ' + DATENAME(MONTH,@date) + ' ' + DATENAME(YEAR, @date)


So as you can see, there are many different way to display a given date and time value. Because of this, Transact-SQL provides the CONVERT function to format date/time values into a number of preset output formats. Also by using the CONVERT function along with one or more additional Transact-SQL functions, such as DATEPART you should be able to display the date any way you need.

That is the end of this tutorial on Working With Dates in Transact-SQL. I hope you found this tutorial informative and helpful, and thank you for reading.

Happy Coding!

Is This A Good Question/Topic? 0
  • +

Replies To: Working With Date Values in SQL Server

#2 Guest_Craig*


Reputation:

Posted 29 March 2010 - 07:10 AM

Great article, but what an abomination!!
Was This Post Helpful? 0

Page 1 of 1