List of all available DateTime format in SQL Server 2005

We, as a SQL Developer, often need to check, change and see different types of available DateTime format in SQL Server. I would like to share one small yet handy script which will list all available DateTime format for you.

set nocount on
DECLARE @Date int
create table #temp
(
Type int,
date varchar(50)
)
set @Date=131
While @Date>=0
begin
if((@Date>=0 and @date<15) or (@Date>=20 and @date<26) or (@Date>=100 and @date<=114) or @Date in (120,121,126,130,131))
insert into #temp
select convert(varchar,@date),convert(varchar,getdate(),@Date)
set @Date=@Date1
end
select * from #temp
drop table #Temp
Here is the result.

Type        date
———– ————————————————–
131         28/04/1430  1:26:57:153PM
130         28 ???? ?????? 1430  1:26:57:1
126         2009-04-23T13:26:57.153
121         2009-04-23 13:26:57.153
120         2009-04-23 13:26:57
114         13:26:57:153
113         23 Apr 2009 13:26:57:153
112         20090423
111         2009/04/23
110         04-23-2009
109         Apr 23 2009  1:26:57:153PM
108         13:26:57
107         Apr 23, 2009
106         23 Apr 2009
105         23-04-2009
104         23.04.2009
103         23/04/2009
102         2009.04.23
101         04/23/2009
100         Apr 23 2009  1:26PM
25          2009-04-23 13:26:57.153
24          13:26:57
23          2009-04-23
22          04/23/09  1:26:57 PM
21          2009-04-23 13:26:57.153
20          2009-04-23 13:26:57
14          13:26:57:153
13          23 Apr 2009 13:26:57:153
12          090423
11          09/04/23
10          04-23-09
9           Apr 23 2009  1:26:57:153PM
8           13:26:57
7           Apr 23, 09
6           23 Apr 09
5           23-04-09
4           23.04.09
3           23/04/09
2           09.04.23
1           04/23/09
0           Apr 23 2009  1:26PM

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Advertisements