PDA

View Full Version : Solved: Date format changing when pushed to SQL Server



MacDaddy
04-05-2011, 03:29 AM
Hi,

I'm having a bit of a problem with dates - nothing new there.

I've developed a front end to a SQL database in Excel from which the data is then pushed into SQL Server via a server-side process.

However, dates are changing format between excel and sql server, sometimes.

I'm using 1st May 2011 and 31st May 2011 to test pushing the data across to the server. All language settings on my client machine and the SQL Server machine are set to English UK and the login used to push the data into the database has its default language set to English UK. Language settings in Excel are also English UK - I've pretty much checked language and locale setting everywhere I can think of as this has caused me similar problems in the past.

However, when using 01/05/2011 the month and the day are switched so my data ends up being entered into the DB as 05/01/2011 (5th January 2011). When using 31/05/2011 everything works ok and it hits the DB using the correct date.

I can't work out whether it's Excel causing the problem or the database, but I'm inclined to think it's Excel. I've tried formatting the date via code in all manner of ways but nothing works, up to the 12th of the month the day and month are being switch, from 13th onwards the date goes across correctly.

I can't think what else to check so if anyone has any idea where I'm going wrong I'd greatly appreciate suggestions.

Bob Phillips
04-05-2011, 08:21 AM
I assume the data is being pushed from VBA?

Have you tried formatting the date in a unambiguous way, such as 01-May-2011?

Kenneth Hobs
04-05-2011, 09:29 AM
A database should only be looking at numbers, not date formats. Your solution probably lies in sending the value as a number. e.g. Use Range("A1").Value2 rather than Range("A1").Value when sending to the database.

MacDaddy
04-05-2011, 11:35 AM
Since my post this morning I've found out a bit more.

Yes, I've tried all sorts of ways of formatting the date, including such formats as 01-May-2011.

However, it seems that when Integration Services picks up the data it sees it as string data rather than a date field and so converts it to the best date format it can at the time.

I have tried formatting the date as integer/long(?) but then Integration Services falls over when it tries to read the excel file as it can't convert the value to a valid date.

So perhaps it isn't an Excel problem after all as I first thought.

MacDaddy
04-06-2011, 01:08 AM
Just managed to crack this and it wasn't an Excel problem at all.

The date fed to the DTS/SSIS process was being read as a date but somewhere was being converted to DT_WSTR (unicode string) and then back to a date, but in the incorrect format.

I did a bit of digging around and managed to find where to change the property of the output back to a date data type.

Now all is well again.

Thanks for your time and suggestions though, much appreciated.

Bob Phillips
04-06-2011, 01:18 AM
BTW, where does SSIS come into this? I had assumed it was a standard SQL update.

MacDaddy
04-06-2011, 01:40 AM
Unfortunately not. It's something I inherited a couple of years back, users enter data into a main spreadsheet and then that data is summarized by a macro and dumped to another file. The SSIS process then picks up this data and uses it to populate the database.

I've redeveloped the main spreadsheet recently so it's far easier to use but haven't got as far as fettling the update process yet so it's still reliant on SSIS to populate the DB.

Sorry if my initial post was misleading.

Bob Phillips
04-06-2011, 01:47 AM
No, not misleading, but you did interest me with the follow-up.

Can you post the SSIS script, just for my interest?