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

4 Responses to “List of all available DateTime format in SQL Server 2005”

  1. Anonymous Says:

    RiteshShah:Nice peace of code. Did a small modification, added a column showing the code to create each example, and added three common situations in a second select statement. 1. How to get today at 00:00:000 time2. How to get today at any time, example uses 18:00 hours3. How to get next hour on the hourSincerely Marten Rune–************** Code START **************set nocount on DECLARE @Date int create table #temp ( Type int, date varchar(50), Code 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),'SELECT convert(varchar,getdate(),' + cast(@Date as varchar) + ')' set @Date=@Date-1 end select * from #temp drop table #Temp SELECT Cast(CONVERT(VARCHAR,getdate(),110) as datetime) AS 'Today 00:00' –No Formatting ,convert(varchar,dateadd(hh,18,Cast(CONVERT(VARCHAR,getdate(),110) as datetime)),20) AS 'Today 18:00' — 20 = format YYYY-MM-DD HH:NN:SS ,convert(varchar,dateadd(hh,datepart(hh,getdate())+1,convert(varchar,getdate(),10)),20) as 'Now next hour on the hour'–************** Code STOPP **************

  2. Anonymous Says:

    RiteshShah:Nice peace of code. Did a small modification, added a column showing the code to create each example, and added three common situations in a second select statement. 1. How to get today at 00:00:000 time2. How to get today at any time, example uses 18:00 hours3. How to get next hour on the hourSincerely Marten Rune–************** Code START **************set nocount on DECLARE @Date int create table #temp ( Type int, date varchar(50), Code 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),'SELECT convert(varchar,getdate(),' + cast(@Date as varchar) + ')' set @Date=@Date-1 end select * from #temp drop table #Temp SELECT Cast(CONVERT(VARCHAR,getdate(),110) as datetime) AS 'Today 00:00' –No Formatting ,convert(varchar,dateadd(hh,18,Cast(CONVERT(VARCHAR,getdate(),110) as datetime)),20) AS 'Today 18:00' — 20 = format YYYY-MM-DD HH:NN:SS ,convert(varchar,dateadd(hh,datepart(hh,getdate())+1,convert(varchar,getdate(),10)),20) as 'Now next hour on the hour'–************** Code STOPP **************

  3. Ritesh Shah Says:

    Hi Marten, Thanks for the script. 🙂 keep up doing good work.

  4. Ritesh Shah Says:

    Hi Marten, Thanks for the script. 🙂 keep up doing good work.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: