April 7, 2009 at 7:10 pm
lots of questions hit SSC on formatting dates to a certain format; the procedure below, sp_dates is really handy to just get that information, as well as the TSQL to produce it.
it produces 21 examples, so you can see at a glance which format might be best for you, or that you might need to use as the basis for a REPLACE or STUFF to get exactly your format.
typical results:
FormattedDate Code SQL
----------------------------------------- ---- ------------------------------------------------------------------------------------
20090407-21:01:47:920 --- SELECT CONVERT(VARCHAR,@date,112) + '-' + CONVERT(VARCHAR,@date,114)
20090407-210147920 --- SELECT CONVERT(VARCHAR,@date,112) + '-' + REPLACE(CONVERT(VARCHAR,@date,114),':','')
04/07/2009 101 SELECT CONVERT(VARCHAR,@date,101)
2009.04.07 102 SELECT CONVERT(VARCHAR,@date,102)
07/04/2009 103 SELECT CONVERT(VARCHAR,@date,103)
07.04.2009 104 SELECT CONVERT(VARCHAR,@date,104)
07-04-2009 105 SELECT CONVERT(VARCHAR,@date,105)
07 Apr 2009 106 SELECT CONVERT(VARCHAR,@date,106)
Apr 07, 2009 107 SELECT CONVERT(VARCHAR,@date,107)
21:01:47 108 SELECT CONVERT(VARCHAR,@date,108)
Apr 7 2009 9:01:47:920PM 109 SELECT CONVERT(VARCHAR,@date,109)
04-07-2009 110 SELECT CONVERT(VARCHAR,@date,110)
2009/04/07 111 SELECT CONVERT(VARCHAR,@date,111)
20090407 112 SELECT CONVERT(VARCHAR,@date,112)
07 Apr 2009 21:01:47:920 113 SELECT CONVERT(VARCHAR,@date,113)
21:01:47:920 114 SELECT CONVERT(VARCHAR,@date,114)
2009-04-07 21:01:47 120 SELECT CONVERT(VARCHAR,@date,120)
2009-04-07 21:01:47.920 121 SELECT CONVERT(VARCHAR,@date,121)
2009-04-07T21:01:47.920 126 SELECT CONVERT(VARCHAR,@date,126)
12 ???? ?????? 1430 9:01:47:9 130 SELECT CONVERT(VARCHAR,@date,130)
12/04/1430 9:01:47:920PM 131 SELECT CONVERT(VARCHAR,@date,131)
and here's the proc itself; stick it in master, and it doesn't matter which db you are in, you have the formats available at a glance:
CREATE PROCEDURE [dbo].[sp_dates](@date as DATETIME=NULL)
AS
BEGIN
IF @date IS NULL
SET @date = getdate()
SELECT CONVERT(VARCHAR(35),@date,101) AS FormattedDate,'101' AS Code,'SELECT CONVERT(VARCHAR(35),@date,101)' AS SQL UNION
SELECT CONVERT(VARCHAR(35),@date,102) AS FormattedDate,'102' AS Code,'SELECT CONVERT(VARCHAR(35),@date,102)' AS SQL UNION
SELECT CONVERT(VARCHAR(35),@date,103) AS FormattedDate,'103' AS Code,'SELECT CONVERT(VARCHAR(35),@date,103)' AS SQL UNION
SELECT CONVERT(VARCHAR(35),@date,104) AS FormattedDate,'104' AS Code,'SELECT CONVERT(VARCHAR(35),@date,104)' AS SQL UNION
SELECT CONVERT(VARCHAR(35),@date,105) AS FormattedDate,'105' AS Code,'SELECT CONVERT(VARCHAR(35),@date,105)' AS SQL UNION
SELECT CONVERT(VARCHAR(35),@date,106) AS FormattedDate,'106' AS Code,'SELECT CONVERT(VARCHAR(35),@date,106)' AS SQL UNION
SELECT CONVERT(VARCHAR(35),@date,107) AS FormattedDate,'107' AS Code,'SELECT CONVERT(VARCHAR(35),@date,107)' AS SQL UNION
SELECT CONVERT(VARCHAR(35),@date,108) AS FormattedDate,'108' AS Code,'SELECT CONVERT(VARCHAR(35),@date,108)' AS SQL UNION
SELECT CONVERT(VARCHAR(35),@date,109) AS FormattedDate,'109' AS Code,'SELECT CONVERT(VARCHAR(35),@date,109)' AS SQL UNION
SELECT CONVERT(VARCHAR(35),@date,110) AS FormattedDate,'110' AS Code,'SELECT CONVERT(VARCHAR(35),@date,110)' AS SQL UNION
SELECT CONVERT(VARCHAR(35),@date,111) AS FormattedDate,'111' AS Code,'SELECT CONVERT(VARCHAR(35),@date,111)' AS SQL UNION
SELECT CONVERT(VARCHAR(35),@date,112) AS FormattedDate,'112' AS Code,'SELECT CONVERT(VARCHAR(35),@date,112)' AS SQL UNION
SELECT CONVERT(VARCHAR(35),@date,113) AS FormattedDate,'113' AS Code,'SELECT CONVERT(VARCHAR(35),@date,113)' AS SQL UNION
SELECT CONVERT(VARCHAR(35),@date,114) AS FormattedDate,'114' AS Code,'SELECT CONVERT(VARCHAR(35),@date,114)' AS SQL UNION
SELECT CONVERT(VARCHAR(35),@date,120) AS FormattedDate,'120' AS Code,'SELECT CONVERT(VARCHAR(35),@date,120)' AS SQL UNION
SELECT CONVERT(VARCHAR(35),@date,121) AS FormattedDate,'121' AS Code,'SELECT CONVERT(VARCHAR(35),@date,121)' AS SQL UNION
SELECT CONVERT(VARCHAR(35),@date,126) AS FormattedDate,'126' AS Code,'SELECT CONVERT(VARCHAR(35),@date,126)' AS SQL UNION
SELECT CONVERT(VARCHAR(35),@date,130) AS FormattedDate,'130' AS Code,'SELECT CONVERT(VARCHAR(35),@date,130)' AS SQL UNION
SELECT CONVERT(VARCHAR(35),@date,131) AS FormattedDate,'131' AS Code,'SELECT CONVERT(VARCHAR(35),@date,131)' AS SQL UNION
SELECT CONVERT(VARCHAR(35),@date,112) + '-' + CONVERT(VARCHAR(35),@date,114) AS FormattedDate,'---' AS Code,'SELECT CONVERT(VARCHAR(35),@date,112) + ''-'' + CONVERT(VARCHAR(35),@date,114)' AS SQL UNION
SELECT CONVERT(VARCHAR(35),@date,112) + '-' + REPLACE(CONVERT(VARCHAR(35),@date,108),':','') AS FormattedDate,'---' AS Code,'SELECT CONVERT(VARCHAR(35),@date,112) + ''-'' + REPLACE(CONVERT(VARCHAR(35),@date,108),'':'','''')' UNION
SELECT CONVERT(VARCHAR(35),@date,112) + '-' + REPLACE(CONVERT(VARCHAR(35),@date,114),':','') AS FormattedDate,'---' AS Code,'SELECT CONVERT(VARCHAR(35),@date,112) + ''-'' + REPLACE(CONVERT(VARCHAR(35),@date,114),'':'','''')' AS SQL
ORDER BY CODE
END
Lowell
April 8, 2009 at 12:39 am
April 8, 2009 at 3:16 am
Well, since almost the same can be viewed in BOL easily, and I have a printout at hand, I probably won't need it - but I'm sure other people will find use for this procedure.
Just one technical thing that might be just a matter of personal attitude, but I don't like when conversion to VARCHAR does not specify length - especially if it is code that other people will regard as something from which they can learn. It is not necessarily an error, but it can cause errors and as such is bad practice.
By the way, if you look at the row with style 130, you will see why - in this case result is truncated at 30 characters. But even if the strings were always shorter, or even if you want the truncation at 30 characters, I still say that not specifying length is bad practice. For example, the "automatic" (default) length can be changed in some future version of SQL Server, and suddenly your queries start returning different results for no apparent reason. Also the "always shorter" argumentation is something one should be very careful about... too often "always" lasts only for a few months or maybe years.
April 8, 2009 at 4:26 am
Hey thanks! it is useful....
"Don't limit your challenges, challenge your limits"
April 8, 2009 at 6:05 am
So here is my formatting function 😉
It's another concept than Lowell's but might also help in some cases. Keep in mind, if possible use CONVERT due to a much better performance!
Edited: Seems that the SQL formatting has problems with this script...
IF (OBJECT_ID('dbo.ufn_FormatDate') IS NOT NULL)
DROP FUNCTION dbo.ufn_FormatDate
GO
CREATE FUNCTION dbo.ufn_FormatDate
/**********************************************************************************
Summary
=======
Function to convert a specified datetime with a specified format string
Parameters
==========
@Date
The datetime to be formatted
@Format
The format string for the specified datetime
Remarks
=======
This funciton is very handy for custom datetime formatting. Anyway the performance is
not the best and it should only be used if CONVERT function doesn't cover the requirements
Formatting parameters:
{yyyy} Four digit years
{yy} Two digit years
{mm} Two digit months
{m} One digit months
{dd} Two digit days
{d} One digit days
{hh} Two digit hours
{h} One digit hours
{nn} Two digit minutes
{n} One digit minutes
{ss} Two digit seconds
{s} One digit seconds
{fff} Three digit milliseconds
{f} One digit milliseconds
{odbc} ODBC timestamp format
{ansi} ANSI datetime format
{edifact} EDIFACT datetime format
{iso8601} ISO8601 date format (also XML)
**********************************************************************************/
(
@Date DATETIME,
@Format VARCHAR(1000)
)
RETURNS VARCHAR(1000)
AS
BEGIN
---------------------------------------------------------
-- Convert special formats
-- {odbc}
IF (CHARINDEX('{odbc}', @Format) != 0)
SET @Format = REPLACE(@Format, '{odbc}', '{ts ''{yyyy}-{mm}-{dd} {hh}:{nn}:{ss}.{fff}''}')
-- {ansi}
IF (CHARINDEX('{ansi}', @Format) != 0)
SET @Format = REPLACE(@Format, '{ansi}', '{yyyy}{mm}{dd}{hh}{nn}{ss}{fff}')
-- {edifact}
IF (CHARINDEX('{edifact}', @Format) != 0)
SET @Format = REPLACE(@Format, '{edifact}', '{yyyy}{mm}{dd}{hh}{nn}')
-- {xml}
IF (CHARINDEX('{iso8601}', @Format) != 0)
SET @Format = REPLACE(@Format, '{iso8601}', '{yyyy}-{mm}-{dd}T{hh}:{nn}:{ss}.{fff}')
---------------------------------------------------------
-- Formatting
-- {yyyy}
IF (CHARINDEX('{yyyy}', @Format) != 0)
SET @Format = REPLACE(@Format, '{yyyy}', CONVERT(CHAR(4), DATEPART(YEAR, @Date)))
-- {yy}
IF (CHARINDEX('{yy}', @Format) != 0)
SET @Format = REPLACE(@Format, '{yy}', RIGHT(CONVERT(CHAR(4), DATEPART(YEAR, @Date)), 2))
-- {mm}
IF (CHARINDEX('{mm}', @Format) != 0)
SET @Format = REPLACE(
@Format,
'{mm}',
CASE
WHEN DATEPART(MONTH, @Date) >= 10 THEN CONVERT(CHAR(2), DATEPART(MONTH, @Date))
ELSE '0' + CONVERT(CHAR(1), DATEPART(MONTH, @Date))
END
)
-- {m}
IF (CHARINDEX('m', @Format) != 0)
SET @Format = REPLACE(@Format, '{m}', CONVERT(VARCHAR(2), DATEPART(MONTH, @Date)))
-- {dd}
IF (CHARINDEX('dd', @Format) != 0)
SET @Format = REPLACE(
@Format,
'{dd}',
CASE
WHEN DATEPART(DAY, @Date) >= 10 THEN CONVERT(CHAR(2), DATEPART(DAY, @Date))
ELSE '0' + CONVERT(CHAR(1), DATEPART(DAY, @Date))
END
)
-- {d}
IF (CHARINDEX('{d}', @Format) != 0)
SET @Format = REPLACE(@Format, '{d}', CONVERT(VARCHAR(2), DATEPART(DAY, @Date)))
-- {hh}
IF (CHARINDEX('{hh}', @Format) != 0)
SET @Format = REPLACE(
@Format,
'{hh}',
CASE
WHEN DATEPART(HOUR, @Date) >= 10 THEN CONVERT(CHAR(2), DATEPART(HOUR, @Date))
ELSE '0' + CONVERT(CHAR(1), DATEPART(HOUR, @Date))
END
)
-- {h}
IF (CHARINDEX('{h}', @Format) != 0)
SET @Format = REPLACE(@Format, '{h}', CONVERT(VARCHAR(2), DATEPART(HOUR, @Date)))
-- {nn}
IF (CHARINDEX('{nn}', @Format) != 0)
SET @Format = REPLACE(
@Format,
'{nn}',
CASE
WHEN DATEPART(MINUTE, @Date) >= 10 THEN CONVERT(CHAR(2), DATEPART(MINUTE, @Date))
ELSE '0' + CONVERT(CHAR(1), DATEPART(MINUTE, @Date))
END
)
-- {n}
IF (CHARINDEX('{n}', @Format) != 0)
SET @Format = REPLACE(@Format, '{n}', DATEPART(MINUTE, @Date))
-- {ss}
IF (CHARINDEX('{ss}', @Format) != 0)
SET @Format = REPLACE(
@Format,
'{ss}',
CASE
WHEN DATEPART(SECOND, @Date) >= 10 THEN CONVERT(CHAR(2), DATEPART(SECOND, @Date))
ELSE '0' + CONVERT(CHAR(1), DATEPART(SECOND, @Date))
END
)
-- {s}
IF (CHARINDEX('{s}', @Format) != 0)
SET @Format = REPLACE(@Format, '{s}', DATEPART(SECOND, @Date))
-- {fff}
IF (CHARINDEX('{fff}', @Format) != 0)
SET @Format = REPLACE(
@Format,
'{fff}',
CASE
WHEN DATEPART(MILLISECOND, @Date) >= 100 THEN CONVERT(CHAR(3), DATEPART(MILLISECOND, @Date))
WHEN DATEPART(MILLISECOND, @Date) >= 10 THEN '0' + CONVERT(CHAR(2), DATEPART(MILLISECOND, @Date))
ELSE '00' + CONVERT(CHAR(1), DATEPART(MILLISECOND, @Date))
END
)
-- {f}
IF (CHARINDEX('{f}', @Format) != 0)
SET @Format = REPLACE(@Format, '{f}', CONVERT(VARCHAR(3), DATEPART(MILLISECOND, @Date)))
RETURN (@Format)
END
GO
-- ///////////////////////////////////////////////////
-- --> Test
DECLARE @dt DATETIME
SELECT @dt = GETDATE()
PRINT 'Input ' + CONVERT(VARCHAR(40), @dt, 121)
PRINT dbo.ufn_FormatDate(@dt, 'yyyy-mm {yyyy}-{mm}')
PRINT dbo.ufn_FormatDate(@dt, 'yyyy-mm-dd {yyyy}-{mm}-{dd}')
PRINT dbo.ufn_FormatDate(@dt, 'yyyy-m-d h:nn {yyyy}-{m}-{d} {h}:{n}')
PRINT dbo.ufn_FormatDate(@dt, 'yyyy-m-d h:n:s {yyyy}-{m}-{d} {h}:{n}:{s}')
PRINT dbo.ufn_FormatDate(@dt, 'yyyy-m-d h:n:s.f {yyyy}-{m}-{d} {h}:{n}:{s}.{f}')
PRINT dbo.ufn_FormatDate(@dt, 'yyyy-m-d h:n:s.fff {yyyy}-{m}-{d} {h}:{n}:{s}.{fff}')
PRINT dbo.ufn_FormatDate(@dt, 'yyyy-mm-dd hh:nn {yyyy}-{mm}-{dd} {hh}:{nn}')
PRINT dbo.ufn_FormatDate(@dt, 'yyyy-mm-dd hh:nn:ss {yyyy}-{mm}-{dd} {hh}:{nn}:{ss}')
PRINT dbo.ufn_FormatDate(@dt, 'yyyy-mm-dd hh:nn:ss.f {yyyy}-{mm}-{dd} {hh}:{nn}:{ss}.{f}')
PRINT dbo.ufn_FormatDate(@dt, 'yyyy-mm-dd hh:nn:ss.fff {yyyy}-{mm}-{dd} {hh}:{nn}:{ss}.{fff}')
PRINT dbo.ufn_FormatDate(@dt, 'm/d/yy {m}/{d}/{yy}')
PRINT dbo.ufn_FormatDate(@dt, 'dd.mm.yyyy {dd}.{mm}.{yyyy}')
PRINT dbo.ufn_FormatDate(@dt, 'ODBC {odbc}')
PRINT dbo.ufn_FormatDate(@dt, 'ANSI {ansi}')
PRINT dbo.ufn_FOrmatDate(@dt, 'ISO8601 (XML) {iso8601}')
-- <-- Test
-- ///////////////////////////////////////////////////
Greets
Flo
April 8, 2009 at 6:10 am
Hi Lowell!
Lowell (4/7/2009)
lots of questions hit SSC on formatting dates to a certain format; the procedure below, sp_dates is really handy to just get that information, as well as the TSQL to produce it.
Nice procedure! Thank's for that!
Only one thing, you should consider to rename your "sp_" to "usp_" or anything else. Have a look to these links:
http://www.sqlservercentral.com/Forums/Topic8041-65-1.aspx
http://www.sqlmag.com/Article/ArticleID/23011/sql_server_23011.html
http://msdn.microsoft.com/en-us/library/dd172115.aspx
Greets
Flo
April 8, 2009 at 6:15 am
florian i named it with sp_ on purpose in this case, and then placed it in the master database; that way i can call it from any database i happen to be working in; i don't want 40 copies, one for each database, when one will do.
I took Vladan's advice and explicitly changed mine to varchar(35) for all the converts.
this has several advantages over BOL for me: it's way faster than calling BOL, letting it load, then searching for convert and date and time...also it gives an EXACT example for each format, ready for copy and paste.
Lowell
April 8, 2009 at 6:21 am
Florian thanks for your function;
one format, the ISO one, is really handy for me, as I have to do a lot of that conversions;
ISO8601 (XML) 2009-04-08T08:20:46.270
perfect!
Lowell
April 8, 2009 at 6:26 am
Lowell (4/8/2009)
florian i named it with sp_ on purpose in this case, and then placed it in the master database; that way i can call it from any database i happen to be working in; i don't want 40 copies, one for each database, when one will do.
That explains! I was a bit surprised that you might not know this ;-).
this has several advantages over BOL for me: it's way faster than calling BOL, letting it load, then searching for convert and date and time...also it gives an EXACT example for each format, ready for copy and paste.
Sure, it's a very nice helper procedure! I usually use a database schema called "Gadgets" for objects like this.
My function an example for another very often called question "how to custom format date?". The performance is really not good and might be heavy improved with a calendar table or at least inline function solution. It's just a little helper I use sometimes.
Greets
Flo
April 8, 2009 at 6:27 am
Lowell (4/8/2009)
Florian thanks for your function;one format, the ISO one, is really handy for me, as I have to do a lot of that conversions;
ISO8601 (XML) 2009-04-08T08:20:46.270
perfect!
You are very welcome!
April 8, 2009 at 4:33 pm
Lowell (4/8/2009)
Florian thanks for your function;one format, the ISO one, is really handy for me, as I have to do a lot of that conversions;
ISO8601 (XML) 2009-04-08T08:20:46.270
perfect!
I'm confused.... isn't that just...
[font="Courier New"]SELECT CONVERT(VARCHAR,GETDATE(),126)[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
April 8, 2009 at 4:37 pm
Florian Reischl (4/8/2009)
So here is my formatting function 😉It's another concept than Lowell's but might also help in some cases. Keep in mind, if possible use CONVERT due to a much better performance!
Edited: Seems that the SQL formatting has problems with this script...
Interesting... how did you get it to format the code in the old style window using the code IF shortcuts?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 8, 2009 at 4:43 pm
[font="Verdana"]I still tend to use a Calendar table, rather than a function, for bulk date conversions. Obviously that doesn't work so well when you have a time component![/font]
April 8, 2009 at 5:17 pm
Didn't you leave out a few numbers?
declare @date datetime
set @date = getdate()
select
convert(varchar(35),@date,a.Code) AS FormattedDate,
Code = convert(varchar(3),a.Code),
convert(varchar(40),'select convert(varchar(35),@date,'+convert(varchar(20),a.Code)+')') as [SQL]
from
(
select Code = 0 union all
select Code = 1 union all
select Code = 2 union all
select Code = 3 union all
select Code = 4 union all
select Code = 5 union all
select Code = 6 union all
select Code = 7 union all
select Code = 8 union all
select Code = 9 union all
select Code = 11 union all
select Code = 12 union all
select Code = 13 union all
select Code = 14 union all
select Code = 20 union all
select Code = 100 union all
select Code = 101 union all
select Code = 102 union all
select Code = 103 union all
select Code = 104 union all
select Code = 105 union all
select Code = 106 union all
select Code = 107 union all
select Code = 108 union all
select Code = 109 union all
select Code = 111 union all
select Code = 112 union all
select Code = 113 union all
select Code = 114 union all
select Code = 120 union all
select Code = 126 union all
select Code = 130 union all
select Code = 131
) a
Results:
FormattedDate Code SQL
----------------------------------- ---- ----------------------------------------
Apr 8 2009 7:14PM 0 select convert(varchar(35),@date,0)
04/08/09 1 select convert(varchar(35),@date,1)
09.04.08 2 select convert(varchar(35),@date,2)
08/04/09 3 select convert(varchar(35),@date,3)
08.04.09 4 select convert(varchar(35),@date,4)
08-04-09 5 select convert(varchar(35),@date,5)
08 Apr 09 6 select convert(varchar(35),@date,6)
Apr 08, 09 7 select convert(varchar(35),@date,7)
19:14:20 8 select convert(varchar(35),@date,8)
Apr 8 2009 7:14:20:487PM 9 select convert(varchar(35),@date,9)
09/04/08 11 select convert(varchar(35),@date,11)
090408 12 select convert(varchar(35),@date,12)
08 Apr 2009 19:14:20:487 13 select convert(varchar(35),@date,13)
19:14:20:487 14 select convert(varchar(35),@date,14)
2009-04-08 19:14:20 20 select convert(varchar(35),@date,20)
Apr 8 2009 7:14PM 100 select convert(varchar(35),@date,100)
04/08/2009 101 select convert(varchar(35),@date,101)
2009.04.08 102 select convert(varchar(35),@date,102)
08/04/2009 103 select convert(varchar(35),@date,103)
08.04.2009 104 select convert(varchar(35),@date,104)
08-04-2009 105 select convert(varchar(35),@date,105)
08 Apr 2009 106 select convert(varchar(35),@date,106)
Apr 08, 2009 107 select convert(varchar(35),@date,107)
19:14:20 108 select convert(varchar(35),@date,108)
Apr 8 2009 7:14:20:487PM 109 select convert(varchar(35),@date,109)
2009/04/08 111 select convert(varchar(35),@date,111)
20090408 112 select convert(varchar(35),@date,112)
08 Apr 2009 19:14:20:487 113 select convert(varchar(35),@date,113)
19:14:20:487 114 select convert(varchar(35),@date,114)
2009-04-08 19:14:20 120 select convert(varchar(35),@date,120)
2009-04-08T19:14:20.487 126 select convert(varchar(35),@date,126)
13 ???? ?????? 1430 7:14:20:487PM 130 select convert(varchar(35),@date,130)
13/04/1430 7:14:20:487PM 131 select convert(varchar(35),@date,131)
April 8, 2009 at 5:41 pm
i left them out on purpose; the format of 0 = format of 100,1=101 etc; just duplicates of the same formats; I thought it's easier to just stick with the three digit numbers
Lowell
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply