We are in the midst of a report writing blitz. While writing Reporting Services reports, we’ve found the need for formatting dates and times in Microsoft SQL Server. Ordinarily, our UI tier handles formatting based on the customer specific rules. We aren’t there yet on the report side, so we need SQL Server to do some of the lifting. For future reference, I’m going to list a few useful functions for turning those pesky datetime values into strings.
Built-in Formatting
When out-of-the-box formatting will do, use the CONVERT function and pass in a format code. Checkout this example:
Executing: print CONVERT(varchar(20), getdate(), 0);
Yields: Dec 2 2010 9:37AM
To see all of the built-in formats in action, execute the following script:
declare @i int, @now datetime
select @i = 0, @now = '2010-12-02 8:05:55'
while @i < 200
begin
begin try
print ltrim(str(@i)) + ': '
+ convert(nvarchar(200), @now, @i)
end try
begin catch
end catch
set @i += 1
end
Custom Formatting
For custom formatting, there is no official way to pass a format string like you would in .NET (think “yy/mm/dd”). In SQL Server you’ll want to resort to using some of the following functions:
Localization
Another thing you might want to look into is changing the language used for formatting dates.
Your mileage may vary here and I’m not sure of the repercussions, so if your machine explodes and kills your dog, remember you were warned!
declare @oldLanguage nvarchar(100)
set @oldLanguage = @@language
set language 'french'
print convert(varchar(20), getdate(), 7)
set language @oldLanguage
Executing the above would result in the current language being set to french, the month name being printed in french and reverting back to your original language.
Hope that helps ease the pain – even if just a little.