Formatted date in SQL Server 2008

We all know that different countries
require different kinds of date formats. Different applications require
different date formats. SQL Server database administrators and developers use
the function convert() in conjunction with other date functions such as datepart(),
datename(), etc. to manipulate the format of the output.

This article illustrates how to create
one common function to get most of the formats required by different countries
and applications.

Let’s create the dbo.format_date function
as shown below. Please download the code from here.

/****** Object:  UserDefinedFunction [dbo].[format_date]

   Script Date: 05/12/2009 23:19:35 ******/

IF  EXISTS (SELECT * FROM sys.objects

   WHERE object_id = OBJECT_ID(N’[dbo].[format_date]’)

   AND type in (N’FN’, N’IF’, N’TF’, N’FS’, N’FT’))

DROP FUNCTION [dbo].[format_date]

GO
/****** Object:  UserDefinedFunction [dbo].[format_date]

   Script Date: 05/12/2009 23:19:18 ******/

SET ANSI_NULLS ON

GO
SET QUOTED_IDENTIFIER ON

GO
SET CONCAT_NULL_YIELDS_NULL OFF

go

CREATE function [dbo].[format_date]

   (@inputdate datetime ,@format varchar(500))

returns varchar(500)

as

begin

declare @year varchar(4)			–YYYY

declare @shortyear varchar(4)			–Yr

declare @quarter varchar(4)			–QQ

declare @month varchar(2)			–MM

declare @week varchar(2)			–WW

declare @day varchar(2)			–DD

declare @24hours varchar(2)			–24HH

declare @12hours varchar(2)			–HH

declare @minutes varchar(2)			–MI

declare @seconds varchar(2)			–SS

declare @milliseconds varchar(3)		–MS

declare @microseconds varchar(6)		–MCS

declare @nanoseconds varchar(9)			–NS

declare @dayname varchar(15)			–DAY

declare @monthname varchar(15)			–MONTH

declare @shortmonthname varchar(15)	    	–MON

declare @AMPM	 varchar(15)		–AMPM

declare @TZ	 varchar(15)		–TZ

declare @UNIXPOSIX	 varchar(15)		–UNIXPOSIX

					–UCASE

					–LCASE
					

declare @formatteddate varchar(500)		
–Assign current date and time to

if (@inputdate is NULL or @inputdate =”)

begin

set @inputdate = getdate()

end
if (@format is NULL or @format =”)

begin

set @format =’YYYY-MM-DD 12HH:MI:SS AMPM’

end
–set all values
set @year		= convert(varchar(4),year(@inputdate))

set @shortyear	= right(@year,2)

set @quarter	= convert(varchar(1),datepart(QQ,(@inputdate)))

set @month		= right(‘0’+convert(varchar(2),month(@inputdate)),2)

set @week		= right(‘0’+convert(varchar(2),datepart(ww,(@inputdate))),2)

set @day		= right(‘0’+convert(varchar(2),day(@inputdate)),2)

set @24hours	= right(‘0’+convert(varchar(2),datepart(hh,@inputdate)),2)

set @TZ		= convert(varchar(10),datename(TZ,convert(varchar(20),@inputdate)))

set @UNIXPOSIX	= convert(varchar(15),datediff(ss,convert(datetime,’01/01/1970 00:00:000’),@inputdate))
if datepart(hh,@inputdate) >12

begin

set @12hours      = right(‘0’+convert(varchar(2),datepart(hh,@inputdate)) -12,2)

end

else

begin

set @12hours      = right(‘0’+convert(varchar(2),datepart(hh,@inputdate)) ,2)

end
if datepart(hh,@inputdate) >11

begin

set @AMPM =’PM’

end

else

begin

set @AMPM =’AM’

end
set @minutes      = right(‘0’+convert(varchar(2),datepart(n,@inputdate)),2)

set @seconds      = right(‘0’+convert(varchar(2),datepart(ss,@inputdate)),2)

set @milliseconds = convert(varchar(3),datepart(ms,@inputdate))

set @microseconds = convert(varchar(6),datepart(mcs,@inputdate))

set @nanoseconds  = convert(varchar(9),datepart(ns,@inputdate))

set @dayname      = datename(weekday,@inputdate)

set @monthname    = datename(mm,@inputdate)

set @shortmonthname= left(datename(mm,@inputdate),3)

set @formatteddate = @format

set @formatteddate=replace(@formatteddate,’MONTH’,@monthname)

set @formatteddate=replace(@formatteddate,’MON’,@shortmonthname)

set @formatteddate=replace(@formatteddate,’AMPM’,@AMPM)
set @formatteddate=replace(@formatteddate,’YYYY’,@year)

set @formatteddate=replace(@formatteddate,’Yr’,@shortyear)

set @formatteddate=replace(@formatteddate,’QQ’,@quarter)

set @formatteddate=replace(@formatteddate,’WW’,@week)

set @formatteddate=replace(@formatteddate,’MM’,@month)

set @formatteddate=replace(@formatteddate,’DD’,@Day)

set @formatteddate=replace(@formatteddate,’24HH’,@24hours)

set @formatteddate=replace(@formatteddate,’12HH’,@12hours)

set @formatteddate=replace(@formatteddate,’Mi’,@minutes)

set @formatteddate=replace(@formatteddate,’SS’,@seconds)

set @formatteddate=replace(@formatteddate,’MS’,@milliseconds)

set @formatteddate=replace(@formatteddate,’MCS’,@microseconds)

set @formatteddate=replace(@formatteddate,’NS’,@nanoseconds)

set @formatteddate=replace(@formatteddate,’DAY’,@dayname)

set @formatteddate=replace(@formatteddate,’TZ’,@TZ)

set @formatteddate=replace(@formatteddate,’UNIXPOSIX’,@UNIXPOSIX)
if charindex(‘ucase’,@formatteddate)<>0

begin

set @formatteddate=replace(@formatteddate,’ucase’,”)

set @formatteddate=upper(@formatteddate)

end
if charindex(‘lcase’,@formatteddate)<>0

begin

set @formatteddate=replace(@formatteddate,’lcase’,”)

set @formatteddate=lower(@formatteddate)

end
return @formatteddate

end
GO

Now let’s use explore the use of function,
using different scenarios. Any of the following keywords can be used in any
combination to display the date in a different format.

Scenario 1

If you want to display the date in
YYYY/MM/DD format, execute the following command.

select dbo.format_date(GETDATE(),’YYYY/MM/DD’) as Date

Result:

Date

————–

2009/01/12

Scenario 2

To display date in YYYY-MM-DD format,
execute the following command.

select dbo.format_date(GETDATE(),’YYYY-MM-DD’) as Date

Result:

Date

——————-

2009-01-12

Scenario 3

To display the year and Quarter, execute
the following command.

select dbo.format_date(GETDATE(),’Year: YYYY, Quarter: QQ ‘) as Date

Result:

Date

———————–

Year: 2009, Quarter: 1

Scenario 4

To display the time in 24 hour format,
execute the following command.

select dbo.format_date(GETDATE(),’24HH:MI:SS’) as Time

Result:

Time

————————

23:10:07

Scenario 5

To display the time in 12 hour format,
execute the following command.

select dbo.format_date(GETDATE(),’12HH:MI:SS AMPM’) as Time

Result:

Time

———————–

11:11:02 PM

Scenario 6

To display date in YYYY/MM/DD and time in
24 hr format, execute the following command.

select dbo.format_date(GETDATE(),’YYYY/MM/DD 24HH:MI:SS’) as DateTime

Result:

DateTime

———————–

2009/01/12 23:11:44

Scenario 7

To display date in YYYY/MM/DD and time in
12 hr format, execute the following command.

select dbo.format_date(GETDATE(),’YYYY/MM/DD 12HH:MI:SS AMPM’) as DateTime

Result:

DateTime

————————-

2009/01/12 11:13:27 PM

Scenario 8

To display date in DD-MM-YY, execute the
following command.

select dbo.format_date(GETDATE(),’DD-MM-YR’) as Date

Result:

Date

————————

12-01-09

Scenario 9

To display date in DDMMYY, execute the
following command.

select dbo.format_date(GETDATE(),’DDMMYR’) as Date

Result:

Date

———————-

120109

Scenario 10

To display the date, time in a format
where you could use it as a suffix for filename, execute the following command.

select dbo.format_date(GETDATE(),’YYYY_MM_DD_HH_MI_SS_MS_MCS_NS’) as FileNameSuffix

Result:

FileNameSuffix

——————————————–

2009_01_12_HH_16_18_200_200000_200000000

Scenario 11

To display the date, time, name of the
month and name of the day, execute the following command.

select dbo.format_date(GETDATE(),’DAY, MONTH DD, YYYY 12HH:MI:SS AMPM’) as DateTime

Result:

DateTime

—————————————

Monday, January 12, 2009 11:20:31 PM

Scenario 12

To display the date, time, name of the
month and name of the day with time offset, execute the following command.

select dbo.format_date(GETDATE(),’DAY MONTH DDth, YR 12HH:MI:SS TZ’) as DateTime

Result:

DateTime

——————————————

Monday January 12th, 09 11:21:42 +00:00

Scenario 13

To display the unix posix time, execute
the following command.

select dbo.format_date(GETDATE(),’Your Unix time is: UNIXPOSIX’) as POSIX

Result:

POSIX

———————————-

Your Unix time is: 1231802580

Scenario 14

To display the year and week, execute the
following.

select dbo.format_date(GETDATE(),’Year: YYYY, Week: WW’) as YearWeek

Result:

YearWeek

————————-

Year: 2009, Week: 03

Scenario 15

To display the date with month name,
execute the following command.

select dbo.format_date(GETDATE(),’YYYY-MONTH-DD’) as Date

Result:

Date

————————–

2009-January-12

Scenario 16

To display the date with short month
name, execute the following command.

select dbo.format_date(GETDATE(),’YYYY-MON-DD’) as Date

Result:

Date

————————

2009-Jan-12

Scenario 17

To display the date with short month name
in upper case, execute the following command.

select dbo.format_date(GETDATE(),’YYYY-MON-DD ucase’) as Date

Result:

Date

———————-

2009-JAN-12

Scenario 18

To display the date with month name in
lower case, execute the following command.

select dbo.format_date(GETDATE(),’YYYY-MONTH-DD lcase’) as Date

Result:

Date

——————-

2009-january-12

Scenario 19

If you do not pass the parameters, the
function uses default method of formatting the date and time. Execute the
following command:

select dbo.format_date(NULL,NULL)

select dbo.format_date(NULL,’YYYY’)

select dbo.format_date(getdate(),NULL)

Result:

Date

———————–

2009-01-12 11:29:17 PM

Year

———————–

2009
Date

———————–

2009-01-12 11:29:17 PM

This article illustrated how to create
one common function, to get most of the date formats required by different
countries and applications.

»


See All Articles by Columnist MAK