How to Format the Date in a SQL Server

by Lysis

Overview

Formatting dates in Microsoft SQL server is a tricky process. The database application does not allow you to format it with a simple function like you can do through ASP code. Instead, the date needs to be converted to a string and formatted using SQL's string functions. After using the string format functions, a customized date display can be returned to the user.
Formatting the Date

Step 1

Create a date variable. The following code sets up a date variable that is used for formatting. The variable is declared and assigned the current date and time of the SQL server. declare @myDate datetime set @myDate = getnow()
Step 2

Create a string to hold the converted date. Since formatting dates requires manipulation using strings, the date variable is first converted to a string. The following code declares a string variable that will hold the converted, formatted date. declare @myDateString varchar(50)
Step 3

Setup variables for the month, day and year. The following code declares string variables for each part of the date. This allows easy manipulation and formatting for later instances of the code. declare @month varchar(2) declare @day varchar(2) declare @year varchar(4)
Step 4

Assign the the month, day and year values to the declared variables. The code below converts the integer values of each part of the date to a string for further manipulation. set @month = convert(month(@myDate), varchar(2)) set @day = convert(day(@myDate), varchar(2)) set @year = convert(year(@myDate), varchar(4))
Step 5

Assign the fully formatted date to the variable declared in step two. The code below formats the date to a standard "m/d/yyyy" display to return to the user. set @myDateString = @month + '/' + @day + '/' + @year
Step 6

Return the formatted date. The following code selects the formatted date and returns it to the user. select @myDateString as FormattedDate

References & Resources