July 4, 2001 at 8:09 pm
Hi all
Im trying to get an equivalent function to the oracle TO_CHAR, for example:
SELECT TO_CHAR(sysdate, 'YYYYMMDD')
will give me
20010705
The datepart function in SQLServer doesnt pad the Month and Day portions, spo this gives me:
select cast(datepart(yyyy, getdate()) as varchar) + '_' + cast(datepart(mm, getdate()) as varchar) + '_' + cast(datepart(dd, getdate()) as varchar)
2001_7_5 which is ugly..
Any easy way out rather than writing up some case statement? perhaps padding based on the len of the returned value (all too much work 😉 )
Cheers
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
July 5, 2001 at 8:42 am
Take a look at the convert statement. Using a style of 121 will give you yyyy-dd-dd, just replace out the dashes and you're there.
Andy
July 5, 2001 at 1:33 pm
This solution from liya_kats is probably the best one. If this is SQL Server 2000, you could convert this to a user-defined function calle to_char and call it anywhere you need to. This would make porting code from Oracle easier. The call would have to include the owner (dbo.to_char) in it.
Steve Jones
July 5, 2001 at 4:07 pm
How about this one:
select convert(varchar(20), getdate(), 112)
returns 20010705
Andy
July 5, 2001 at 7:23 pm
Thanks Guys, clean and simple solution.
Best Regards
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
December 8, 2002 at 5:00 pm
quote:
This solution from liya_kats is probably the best one. If this is SQL Server 2000, you could convert this to a user-defined function calle to_char and call it anywhere you need to. This would make porting code from Oracle easier. The call would have to include the owner (dbo.to_char) in it.Steve Jones
Plus 1 vote for this approach. UDFs are the best new feature of SS2K after indexed views. Amazing that Access has had them for some time and the "Premier" data base is just now getting around to it.
February 10, 2021 at 9:42 am
This was removed by the editor as SPAM
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply