SqlDatareader GetValue returning date value acording to system date format

Question

Hi i am reading a value from a table using SqlDatareader, problem is it is always formatting the value acording to the machine date format settings.

for example the original date format in the source table is saved as yyyy/mm/dd

when i use SqlDatareader.GetValue in a machine that has date set as MM/dd/YY it is atutomatically converted to that format

is there anyway to retrive the date value with the original date formatting intact?

thanks

Answer 1

Probably not what you're looking for. But if you want to store the formatted date time, may be you should rather use VARCHAR or CHAR(N) as the field type. I would think the DATETIME field is to store datetime and the format it self isn't that important (or what it is meant for). However, you could reconvert it back to that format in C#.

Answer 2

There is no "original date formatting", dates are kept internally as numbers, not strings.

Within a SQL query, you can choose the output formatting with

CONVERT(varchar(30), theColumn, nnn)

where "nnn" is one of the common date formats listed here: MSDN.

Personally I find that page confusing, so another (more useful) page is here: SQL Server Helper. From that page:

CONVERT(VARCHAR(20), GETDATE(), 100) 

will return 'Jan 1 2005 1:29PM'