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.
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.