Culture specific date formatting?

I would like to query some tables and return culture-aware date values.

For instance,

I have three columns BegDate, EndDate, and Year.

4/1,
8/31,
2000

are the values respectively.

In some cultures, the month / date is represented as date . month.
So, 4/1 becomes 1.4

Is there anyway to pull these date values for the specific culture that the user has set, using just SQL?
LVL 8
gregasmAsked:
Who is Participating?
 
Anthony PerkinsCommented:
>>Which would output different string values depending on the cultural settings of the particular windows machine...<<
Think about it this way:  Cultural settings for which computer?  The server that is half the way around the world or the client?  Again, detecting the cultural settings is best handled at the client level.
0
 
Anthony PerkinsCommented:
>>Is there anyway to pull these date values for the specific culture that the user has set, using just SQL?<<
No.  This is best handled in the front-end.  You can set the format using CONVERT and setting the style property.  However this is manual process.  In other words to get the german format you would use something like this:
CONVERT(varchar(10), GETDATE(), 104) --> 27.08.2004

Of course, if you wanted you could pass the style in from the front-end.
0
 
jdlambert1Commented:
You could use the DATEPART (or other date/time functions like MONTH or DATENAME) to parse a datetime value, CAST each piece you parse to a varchar datatype, and concatenate them together (with the plus symbol), to form any format you want.

You could define this formatting in a view, in which case, you'd need a separate view for each cultural variation.

You could also define the formatting in a stored procedure, in which case, you could have one stored procedure per cultural variation, or you could have a single stored procedure that could output different formats depending on a culture variable that you pass in to it.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
gregasmAuthor Commented:
wow, jdlambert1, I feel carpal tunnel just thinking about all that work.. hahah =]]

I'm looking for a simple one, like equivalent to

Format("4/1/2003", "General Date")

Which would output different string values depending on the cultural settings of the particular windows machine...
0
 
jdlambert1Commented:
The only simple solution is very limited, and that's the Style parameter of the CONVERT function that Anthony cited above. For available styles, open SQL Server Books Online and go to "Convert" in the index, then choose the "CAST and CONVERT" topic.

A word of warning: Test CONVERT's output, don't just rely on the style examples in BOL. You can use:

SELECT Convert(varchar(22), GetDate(), 101)

and replace 101 with the various style codes listed.
0
 
gregasmAuthor Commented:
tha'ts a great point,.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.