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