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)
3 replies on “SQL Server CONVERT()”
To really format a date up, I’ve found that you often need to break it up using the datepart functions:
SELECT YEAR(date_field) + '/' + MONTH(date_field) + [etc]Which is way more verbose than it needs to be. With to_char() it is much easier:
SELECT to_char(date_field, ‘YYYY MON DD’) AS formatted_date
Having to pull out each part of the date and then concatenate them together is just way more verbose than it needs to be.
[…] slick features, but one area where they are definitely sucking is date manipulation and formatting. Joseph complained about this a while ago, but it’s time to me to chime in […]