RSS
Feed
Thursday, April 23, 2009 | 
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
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
2 comments:
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 time
2. How to get today at any time, example uses 18:00 hours
3. How to get next hour on the hour
Sincerely 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 **************
Hi Marten, Thanks for the script. :) keep up doing good work.
Post a Comment