SQL-Server Blog of Ritesh Shah --Fight the fear of SQL with SQLHub.com

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=@Date-1

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