Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted May 20, 2009

Formatted date in SQL Server 2008

By Muthusamy Anantha Kumar aka The MAK

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.

YYYY - Year in YYYY Format including century
Yr - Year in YY format
QQ - Display Quarter
MM - Display Month
WW - Diplay Week
DD - Display day
24HH - Display hour in 24 hr format
12HH - Display hour in 12 hr format
MI - Display minutes
SS - Display seconds
MS - Display Milliseconds
MCS - Display MicroSeconds
NS - Display NanoSeconds
DAY - Display day name example: Monday
MONTH- - Display Month name example: August
MON - Display short month name example: Aug
AMPM - Display AM / PM for 12 hr format
TZ - Display time offset
UNIXPOSIX - Display unix posix time. Number of seconds from 1/1/1970
UCASE - Display the result in upper case
LCASE - Display the result in lower case

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM