SQL Data reader returning wrong date

Question

I'm working on an Excel add in, retrieving data from a SQL Server database and inserting it into Excel.

In the database I have a date 2013-06-01 (YYYY-MM-DD)

When reading from the database using SqlDataReader, the following command:

myReader.GetValue(c)

Returns 06/01/2013 (DD-MM-YYYY), when is then stored in Excel taking 6 as the day and 1 as the month number

Any ideas what could be going on?

Thanks

What datatype is your SQL Server column? It ought to be DATE or DATETIME, and then you should be able to properly read it using myReader.GetDateTime(c) — marc_s
– marc_s, Commented Jul 10, 2013 at 9:05
it's dateTime, myReader.GetDateTime(c) still returns the wrong value 06/01/2013 — Karl
– Karl, Commented Jul 10, 2013 at 9:07
When you are putting it into excel, can you not just reformat the datetime as a string: myReader.GetDateTime(c).ToString("yyyy-MM-dd"); — BaconSah
– BaconSah, Commented Jul 10, 2013 at 11:08

Azrael · Accepted Answer · 2013-07-10 09:03:15Z

2

This could be an issue regarding the localization.
You can both set a localization to the SQL connection and the system you are working on, even the column-specification in excel.
My best guess would be to check wether the settings are fine.

answered Jul 10, 2013 at 9:03

Azrael

1612 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

HuorSwords · Accepted Answer · 2013-07-10 09:15:35Z

0

This behavior seems to be produced by a different Localization configuration between the SQL Server and Microsoft Excel.

If you can't change this configuration, I suggest you to use a CAST() or CONVERT() for the SQL statement that you are using to retrieve the data.

In example:

SELECT CONVERT(DATETIME, Date_Column, 103) AS UsingConvertFrom_DDMMYYYY
FROM TABLE_NAME

Using this approach (that is not the best), you are forcing the value of the DataReader.GetValue to use the original value "well parsed".

You can check the MSDN documentation for more details.

edited Jul 10, 2013 at 9:15

answered Jul 10, 2013 at 9:09

HuorSwords

2,2831 gold badge21 silver badges35 bronze badges

2 Comments

Karl Over a year ago

the problem is before the date goes to excel, its in the DataReader that the date is read wrongly

jwg Over a year ago

SQL stores the date as a date, not a string.

Collectives™ on Stack Overflow

SQL Data reader returning wrong date

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related