September 3, 2007 at 9:18 pm
I have a concatenated string field in which I want to include a date. To do this, I've CASTed the date as a VARCHAR. I'm unhappy with the date format, which is American. My users will want to see a British date format (some derivative of dd/mm/yy). I've tried including a CONVERT on the date before I cast it, but the CAST seems to override this.
How can I customise the date format when CASTing a date as VARCHAR?
September 3, 2007 at 10:08 pm
It sounds like you are using a datetime return type when doing the CONVERT, THEN doing the CAST, as using CONVERT with a return type of varchar will do exactly what you want.
SELECT CONVERT(varchar(10), Getdate(), xxx) should do exactly what you are asking for (replacing xxx with the style code of your choosing).
September 3, 2007 at 10:13 pm
Ah yes, that's done it, wonderful! Thanks David. I feel like a bit of a goose.
September 3, 2007 at 11:02 pm
Don't feel bad Sam, as it's a problem many of us had when getting familiar with the internals of dates. The problem is that a line such as "Convert(datetime, Getdate(), 112)" would seem to logically mean that the date would be stored internally as 20070904, for instance, but in reality, SQL Server stores dates basically as a pair of numbers, one for date, one for time, with no formatting at all stored with them. So...
DECLARE
@TestDate datetime
SET
@TestDate = Convert(datetime, Getdate(), 112)
SELECT
@TestDate
seems like it should display 20070904, but in reality, since the Convert function's return type is datetime, SQL Server stores it in its internal datetime format (thus stripping the style we applied), so the SELECT statement just formats the datetime value it finds into your local setting. Casting that into a varchar will then take your localized format, and turn it into a string.
September 3, 2007 at 11:25 pm
By the way, my initial code does strip off the time portion of the date, so if you need the time, just increase (or decrease, such as style 112 to varchar(8)) the size of the varchar in the convert function to whatever you need for your purposes.
September 3, 2007 at 11:34 pm
Fantastic, thanks for such an informative response.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply