I recently had the need format a datetime field in SQL Server 2000. My first thought was to look for to_char(), which does the job in PostgreSQL and Oracle. No luck there because SQL Server doesn’t have a to_char() function. After some hunting around I found out about the convert() function. This does the same thing as to_char() (more or less) but instead of using traditional date formatting codes, “style codes”.
Yes, you read that right. Instead of being able to get a four digit year by specifying Y or YYYY, you have to look up the style code that matches the format you want. In my case I was look for DD MON YYYY format. This turns out to be style code 106. Here is an example:
SELECT CONVERT(VARCHAR(11), datetime_field, 106) AS formatted_date
Some time with Google turned up a page listing examples of various style codes. Here is another one about 40% down the page.
There is an alternative to convert(), datepart(). This allows you to pull out specific parts of the datetime field. So you could do the same thing as the convert() example above, but it would be much more verbose.
UPDATE Fri 7 Jul 2006 @ 11:30am : I really should have included a list at least some of the format codes in this post for future reference:
- 100 – mon dd yyyy hh:miAM (or PM)
- 101 – mm/dd/yy
- 106 – dd mon yy
- 108 – hh:mm:ss
- 109 – mon dd yyyy hh:mi:ss:mmmAM (or PM)
- 130 – dd mon yyyy hh:mi:ss:mmmAM (or PM)