October 7, 2013 at 12:37 am
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.
October 18, 2013 at 9:14 am
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)
October 18, 2013 at 9:50 am
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.
October 18, 2013 at 12:22 pm
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
November 6, 2013 at 12:06 pm
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(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
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.
April 27, 2016 at 2:03 pm
Thanks for the script. There have been a few of these lately and I had to go back and look yours up.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy