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 …)