convert(varchar, getdate(), 106) -- Displays a date in the format 25 Jan 2008That is all fine and good if your requirements are satisfied with the formats available inside SQL Server. SQL Server does provide a decent number of formats. Here is the list right from the SQL Server help:
| Without century (yy) | With century (yyyy) | Standard | Input/Output** |
|---|---|---|---|
| - | 0 or 100 (*) | Default | mon dd yyyy hh:miAM (or PM) |
| 1 | 101 | USA | mm/dd/yy |
| 2 | 102 | ANSI | yy.mm.dd |
| 3 | 103 | British/French | dd/mm/yy |
| 4 | 104 | German | dd.mm.yy |
| 5 | 105 | Italian | dd-mm-yy |
| 6 | 106 | - | dd mon yy |
| 7 | 107 | - | Mon dd, yy |
| 8 | 108 | - | hh:mm:ss |
| - | 9 or 109 (*) | Default + milliseconds | mon dd yyyy hh:mi:ss:mmmAM (or PM) |
| 10 | 110 | USA | mm-dd-yy |
| 11 | 111 | JAPAN | yy/mm/dd |
| 12 | 112 | ISO | yymmdd |
| - | 13 or 113 (*) | Europe default + milliseconds | dd mon yyyy hh:mm:ss:mmm(24h) |
| 14 | 114 | - | hh:mi:ss:mmm(24h) |
| - | 20 or 120 (*) | ODBC canonical | yyyy-mm-dd hh:mi:ss(24h) |
| - | 21 or 121 (*) | ODBC canonical (with milliseconds) | yyyy-mm-dd hh:mi:ss.mmm(24h) |
| - | 126(***) | ISO8601 | yyyy-mm-dd Thh:mm:ss:mmm(no spaces) |
| - | 130* | Kuwaiti | dd mon yyyy hh:mi:ss:mmmAM |
| - | 131* | Kuwaiti | dd/mm/yy hh:mi:ss:mmmAM |
/*
Type: Function
Name: dbo.fnFormatDate
Author: Boyan Kostadinov
Created: 01.25.2008
Dependencies: master.dbo.fn_pcre_replace
Parameters: @inputDate(datetime)
- The date to format
@formatString(varchar)
- the format string to use (Examples "dd mm yyyy", "mmm.dd.yy")
Description:
Formats a given date based on the format specified in @formatString
d - one digit day (when applicable)
dd - two digit day
ddd - short day name
dddd - long day name
m - one digit month (when applicable)
mm - two digit month
mmm - short month name
mmmm - long month name
yy - two digit year
yyyy - four digit year
*/
create function dbo.fnFormatDate
(
@inputDate datetime,
@formatString varchar(25)
)
returns varchar(20) as
begin
declare @returnValue varchar(25)
-- Declare local vairables
declare @formattedDate varchar(25),
@day varchar(20), @month varchar(20), @year varchar(20),
@dayFormat varchar(5), @monthFormat varchar(5), @yearFormat varchar(5)
set @dayFormat = ''
set @monthFormat = ''
set @yearFormat = ''
-- Convert the supplied date to day mon year (25 Jan 2008)
set @formattedDate = convert(varchar, @inputDate, 106)
-- If the format string contains a format for the day
if charindex('d', @formatString) > 0
-- Get the day format string
set @dayFormat = master.dbo.fn_pcre_replace(@formatString, '.*?(d{1,4}).*', '$1')
-- If the format string contains a format for the month
if charindex('m', @formatString) > 0
-- Get the month format string
set @monthFormat = master.dbo.fn_pcre_replace(@formatString, '.*?(m{1,4}|M{1,4}).*', '$1')
-- If the format string contains a format for the year
if charindex('y', @formatString) > 0
-- Get the year format string
set @yearFormat = master.dbo.fn_pcre_replace(@formatString, '.*?(y{2,4}).*', '$1')
-- Format the day value based on the format string for the day
select @day =
case @dayFormat
when 'dd' then master.dbo.fn_pcre_replace(@formattedDate, '^(\d+).*', '$1')
when 'ddd' then substring(datename(dw, @formattedDate), 1, 3)
when 'dddd' then datename(dw, @formattedDate)
else convert(varchar, day(@formattedDate))
end
-- Format the month value based on the format string for the month
select @month =
case @monthFormat
when 'mm' then master.dbo.fn_pcre_replace(convert(varchar, @inputDate, 101), '^(\d+)/.*', '$1')
when 'mmm' then master.dbo.fn_pcre_replace(@formattedDate, '\d+\s(\w+)\s\d+', '$1')
when 'mmmm' then datename(m, @formattedDate)
else convert(varchar, month(@formattedDate))
end
-- Format the year value based on the format string for the year
select @year =
case @yearFormat
when 'yy' then substring(convert(varchar, year(@formattedDate)), 3, 2)
else convert(varchar, year(@formattedDate))
end
set @returnValue = @formatString
-- If the day format was specified
if @dayFormat <> ''
-- Replace the day format string with the actual day value
set @returnValue = master.dbo.fn_pcre_replace(@returnValue, @dayFormat, @day)
-- If the month format was specified
if @monthFormat <> ''
-- Replace the month format string with the actual month
set @returnValue = master.dbo.fn_pcre_replace(@returnValue, @monthFormat, @month)
-- If the year format was specified
if @yearFormat <> ''
-- Replace the year format string with the actual year
set @returnValue = master.dbo.fn_pcre_replace(@returnValue, @yearFormat, @year)
-- Return the formated value
return @returnValue
end
To test this function, I created a table that hold the following date format string: formatStringI tested the function with the simple SQL query:
-------------------------
dd MMM yy
dd MMM yyyy
dd-MM-yy
dd-MM-yyyy
dd.MM.yy
dd.MM.yyyy
dd/MM/yy
dd/MM/yyyy
ddMMMyy
ddMMMyyyy
MM-dd-yy
MM-dd-yyyy
MM/dd/yy
MM/dd/yyyy
MMM dd yyyy
MMM dd, yy
MMM dd, yyyy
MMMdd,yyyy
MMMddyyyy
yy.MM.dd
yy/MM/dd
yyMMdd
yyyy-MM-dd
yyyy.MM.dd
yyyy/MM/dd
yyyyMMdd
MMMyyyy
select df.formatString,And here are the results:
dbo.fnFormatDate(getdate(), df.formatString)
as formattedDate
from dateFormats as df
formatString formattedDate
------------------------- --------------------
dd MMM yy 25 Jan 08
dd MMM yyyy 25 Jan 2008
dd-MM-yy 25-01-08
dd-MM-yyyy 25-01-2008
dd.MM.yy 25.01.08
dd.MM.yyyy 25.01.2008
dd/MM/yy 25/01/08
dd/MM/yyyy 25/01/2008
ddMMMyy 25Jan08
ddMMMyyyy 25Jan2008
MM-dd-yy 01-25-08
MM-dd-yyyy 01-25-2008
MM/dd/yy 01/25/08
MM/dd/yyyy 01/25/2008
MMM dd yyyy Jan 25 2008
MMM dd, yy Jan 25, 08
MMM dd, yyyy Jan 25, 2008
MMMdd,yyyy Jan25,2008
MMMddyyyy Jan252008
yy.MM.dd 08.01.25
yy/MM/dd 08/01/25
yyMMdd 080125
yyyy-MM-dd 2008-01-25
yyyy.MM.dd 2008.01.25
yyyy/MM/dd 2008/01/25
yyyyMMdd 20080125
MMMyyyy Jan2008
Links:
[1] http://blog.tech-cats.com/2007/09/using-regular-expression-in-sql-server.html