Date time functions (frustrations) in T-SQL.
Current date
Functions getdate and getutcdate display the current date. Both functions accept zero arguments.
1> print getdate() |
|
2> print getutcdate() |
|
3> go |
|
Sep 18 2006 8:25PM |
|
Sep 18 2006 6:25PM |
Formatting
To format the display of your date you need to convert it to a varchar (like the to_char function in oracle).
So to display 18 september as 25-09-2006 I must convert it the Italian?? way, as shown below.
1> print convert( varchar(32), getdate(), 105) |
|
2> go |
|
18-09-2006 |
|
1> print convert( varchar(32), getdate(), 5) |
|
2> go |
|
18-09-06 |
Advanced(?) formatting
My requirement is simple: get the date displayed as ‘yyyymmdd_hhmiss’. And yes I want leading zeros for the first nine monts/minutes. So ’09’ for ‘September’ and not ‘9’. How hard can that be … Well, I am missing something bigtime OR this basic functionality is simply missing from sqlserver T-SQL (version 2000).
I wrote the T-SQL code below to get me the datetime in the format ‘yyyymmdd_hhmiss’;
1> set nocount on |
|
2> |
|
3> declare @yy varchar(4) |
|
4> , @mm varchar(2) |
|
5> , @dd varchar(2) |
|
6> , @hh varchar(2) |
|
7> , @mi varchar(2) |
|
8> , @ss varchar(2) |
|
9> , @ts varchar(15) |
|
10> |
|
11> set @yy = datepart( yy, getdate()) |
|
12> set @mm = datepart( mm, getdate()) |
|
13> set @dd = datepart( dd, getdate()) |
|
14> set @hh = datepart( hh, getdate()) |
|
15> set @mi = datepart( mi, getdate()) |
|
16> set @ss = datepart( ss, getdate()) |
|
17> |
|
18> |
|
19> if ( cast( @mm as int) < 10) set @mm = '0' + @mm |
|
20> if ( cast( @dd as int) < 10) set @dd = '0' + @dd |
|
21> if ( cast( @hh as int) < 10) set @hh = '0' + @hh |
|
22> if ( cast( @mi as int) < 10) set @mi = '0' + @mi |
|
23> if ( cast( @ss as int) < 10) set @ss = '0' + @ss |
|
24> |
|
25> |
|
26> set @ts = @yy + @mm + @dd + '_' + @hh + @mi + @ss |
|
27> print @ts |
|
28> |
|
29> go |
|
20060918_183339 |
I can not believe this is not standard functionality … Please correct me if I am wrong (As I’m an Oracle DBA very new to MSSQL …)