Function to format a datetime variable by a mask

Question

Function to format a datetime variable by a mask

colin.frame

Default port

Points: 1483
More actions
October 7, 2013 at 12:37 am

#283989

Comments posted to this topic are about the item Function to format a datetime variable by a mask
Forum Etiquette: How to post data/code on a forum to get the best helpMake sure that you include code in the appropriate IFCode tags, e.g. <your code here>. You can find the IFCode tags under the INSERT options when you are writing a post.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQLVoila Mr or Mrs. 500 Points: 564 More actions · Answer 1

Neat but I also use old style CONVERT(). seems simpler and probably more efficient. Just supply the proper [style] code.

/*

http://msdn.microsoft.com/en-us/library/ms187928%28v=sql.105%29.aspx

100mon dd yyyy hh:miAM (or PM)

101mm/dd/yyyy

102yy.mm.dd

103dd/mm/yyyy

104dd.mm.yy

105dd-mm-yy

106dd mon yy

107Mon dd, yy

108hh:mi:ss

109mon dd yyyy hh:mi:ss:mmmAM (or PM)

110mm-dd-yy

111yy/mm/dd

112yymmdd

113dd mon yyyy hh:mi:ss:mmm(24h)

114hh:mi:ss:mmm(24h)

120yyyy-mm-dd hh:mi:ss(24h)

121yyyy-mm-dd hh:mi:ss.mmm(24h)

126yyyy-mm-ddThh:mi:ss.mmm (no spaces)

127yyyy-mm-ddThh:mi:ss.mmmZ

130dd mon yyyy hh:mi:ss:mmmAM

131dd/mm/yy hh:mi:ss:mmmAM

*/

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

SELECT CONVERT(VARCHAR(24),GETDATE(),121)

colin.frame Default port Points: 1483 More actions · Answer 2

Agreed, if you can use one of the standard styles.

The initial triggers for me writing this were a couple of requests that didn't seem to fit in with one of those. Using a mask enables you to define an alternative format e.g. use mask 'YYYYMMDD-hhmi' to get 20131018-1645.

Forum Etiquette: How to post data/code on a forum to get the best helpMake sure that you include code in the appropriate IFCode tags, e.g. <your code here>. You can find the IFCode tags under the INSERT options when you are writing a post.

Norbert L. Muth, M.A. SSC Enthusiast Points: 112 More actions · Answer 3

Hallo,

very good function often needed.

German SQLservers need adaptation.

Since Wednesday in German "Mittwoch" gets corrupted by the MI=minute mask, some workarounds are necessary. I added month and day without leading zero.

/*

YYYY - Year, 4 digit

YY - Year, 2 digit

MMMM - Monats-Name

MMM - Monats-Name, erste 3 Buchstaben

MM - Monat 2-stellig, führende 0

XM - Monat ohne führende 0

DDDD - Wochentag

WOTAG- Wochentag

DDD - Wochentag, erste 3 Buchstaben

DD - Tag 2 stellig führende 0

XD - Tag ohne führende 0

hh - Stunde (24 Stunden, 2 stellig, führende 0

mi - Minuten, 2 stellig, führende 0

ss - Sekunden, 2 stellig, führende 0

*/

alter FUNCTION [dbo].[DateFormattedByMask]

(

@InputDate DATETIME,

@Mask NVARCHAR(40)

)

RETURNS VARCHAR(40)

AS

BEGIN

SELECT @Mask = REPLACE(@Mask,'YYYY',DATENAME(YEAR,@InputDate))

SELECT @Mask = REPLACE(@Mask,'YY',RIGHT(DATENAME(YEAR,@InputDate),2))

SELECT @Mask = REPLACE(@Mask,'MMMM', DATENAME(MONTH,@InputDate))

SELECT @Mask = REPLACE(@Mask,'MMM', LEFT(DATENAME(MONTH,@InputDate),3))

SELECT @Mask = REPLACE(@Mask,'MM', RIGHT('0'+CAST(MONTH(@InputDate) AS NVARCHAR(2)),2))

SELECT @Mask = REPLACE(@Mask,'XM', CAST(MONTH(@InputDate) AS NVARCHAR(2)))

SELECT @Mask = REPLACE(@Mask,'DDDD', 'WOTAG')

SELECT @Mask = REPLACE(@Mask,'DDD', LEFT(DATENAME(dw,@InputDate),3))

SELECT @Mask = REPLACE(@Mask,'DD', RIGHT('0'+DATENAME(DAY,@InputDate),2))

SELECT @Mask = REPLACE(@Mask,'XD', DATENAME(DAY,@InputDate))

SELECT @Mask = REPLACE(@Mask,'hh', RIGHT('0'+DATENAME(hh,@InputDate),2))

SELECT @Mask = REPLACE(@Mask,'mi', RIGHT('0'+DATENAME(mi,@InputDate),2))

SELECT @Mask = REPLACE(@Mask,'ss', RIGHT('0'+DATENAME(ss,@InputDate),2))

SELECT @Mask = REPLACE(@Mask,'WOTAG', DATENAME(dw,@InputDate))

RETURN @Mask

END

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

Maybe something like this could perform better, based on the following article:

http://www.sqlservercentral.com/articles/T-SQL/91724/

CREATE FUNCTION [dbo].[DateFormattedByMask]

(

@InputDate DATETIME,

@Mask NVARCHAR(80)

)

RETURNS TABLE

AS

RETURN

(

SELECT Mask = REPLACE(

REPLACE(

REPLACE(@Mask,'hh', RIGHT('0'+DATENAME(hh,@InputDate),2))

,'mi', RIGHT('0'+DATENAME(mi,@InputDate),2))

,'ss', RIGHT('0'+DATENAME(ss,@InputDate),2))

,'YYYY',DATENAME(YEAR,@InputDate))

,'YY',RIGHT(DATENAME(YEAR,@InputDate),2))

,'MMMM', DATENAME(MONTH,@InputDate))

,'MMM', LEFT(DATENAME(MONTH,@InputDate),3))

,'MM', RIGHT('0'+CAST(MONTH(@InputDate) AS NVARCHAR(2)),2))

,'DDDD', DATENAME(dw,@InputDate))

,'DDD', LEFT(DATENAME(dw,@InputDate),3))

,'DD', RIGHT('0'+DATENAME(DAY,@InputDate),2))

)

GO

EDIT: format and minute code issues.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 5

Thanks for the script. There have been a few of these lately and I had to go back and look yours up.