Change of server - datetime data type prob

Hi

Got a question

I have moved my fully working web app from one server to another (including database).

When I try and do an Insert to the database I get the following error message from the new server.

"Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. "

So I decided to do a little testing.  I outputted the sql that the insert was trying to do and had a play in query analyzer.

I noticed that if you do this:

INSERT INTO tblTest(title,articledate) VALUES ('this is a test', '30/05/2005 0:0')

You get the following error:

"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated."

But if you change the date to be: 05/30/2005 0:0 it inserts fine.

I tried the test insert about on both sql servers (dev and live) and got the same results.  

Does anyone know why doing an insert through one front-end like so:

INSERT INTO tblTest(title,articledate) VALUES ('this is a test', '30/05/2005 0:0')

works and doesnt work on another?  Its working fine on the IIS6 server (dev) but not on the IIS5 server (live) that i'm trying to move over to.

Anyone know a fix?




Jackass03Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hongjunCommented:
This has got to do with regional settings.
You can try yyyy/mm/dd format to make all work.


INSERT INTO tblTest(title,articledate) VALUES ('this is a test', '2005/05/30 0:0')
Jackass03Author Commented:
Yeah I mentioned above that that will work.

I have lots of inserts in my web app so I dont fancy changing them all.

I was just wondering why it works fine on one server and not the other?

I tested on the sql server and got the same results, so it must be on the web server?

The one it works fine on is W2k3 IIS6 and the live one is W2k IIS 5

Any ideas?

Thanks
Jackass03Author Commented:
Is it possible to change the regional settings on the a database in sql server that wont affect other databases on the sql server?
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

hongjunCommented:
Ok, if you intend to insert using mm/dd/yyyy format, then make sure your machine Regional Settings date/time is set as that.

Control -> Regional and Language Options
Make sure you choose US in this case.


hongjun
hongjunCommented:
Just change the ones that do not match with the ones you want.
Jackass03Author Commented:
Hi

Sorry misread your suggestion.  I tried:

INSERT INTO tblTest(title,articledate) VALUES ('this is a test', '2005/05/30 0:0')

On the sql server using query analyzer and it inserted fine.

When i tried it through the web front end i got the error:

"Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. "

I'm guessing it to do with the different environment, but not sure how to fix !

Hope someone can help
hongjunCommented:
How do you insert?
Jackass03Author Commented:
Dont really want to change my seeting as I'm in the UK.

As far as I can see the database setup on dev and live are the same, the same scripts fail and work using query analyzer.

However if I pass through a date in the format:

dd/mm/yyyy on my dev box (IIS 6 it inserts fine)

If I pass through a date in the format dd/mm/yyyy on my live box (IIS5)

I get the error:

"Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. "

If I try and pass the date format as yyyy/mm/dd on my dev box (IIS6) I get:

"Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. "

and if I pass the date format yyyy/mm/dd on my live box (IIS5) it inserts fine.

Any ideas?
hongjunCommented:
Or you could try

strsql = "INSERT INTO tblTest(title,articledate) VALUES ('this is a test', '" & DateValue(2005, 5, 30) & "')"
Jackass03Author Commented:
Just doing a normal insert:

articledate =  request.form("txtArticleDay") & "/" & request.form("txtArticleMonth") & "/" & request.form("txtArticleYear") & " " & request.form("txtArticleHour") & ":" & request.form("txtArticleMin")

strSQL = "INSERT INTO tblTest(title,articledate) VALUES ('this is a test', '" & articledate & "')"

I think its more to do with IIS than the SQL server because it should error when I pass in a date in the format dd/mm/yyyy but it does not on my dev box but it does on live.

Any ideas?

thanks for your help
Jackass03Author Commented:
On my dev box (IIS6) and SQL Server 2k

I can do the following insert without errors in directly on the sql server through query anlyzer

INSERT INTO tblTest(title,articledate) VALUES ('this is a test', '2005/12/30 0:0')

If I do the same insert through a web front end -

articledate =  request.form("txtArticleDay") & "/" & request.form("txtArticleMonth") & "/" & request.form("txtArticleYear") & " " & request.form("txtArticleHour") & ":" & request.form("txtArticleMin")

strSQL = "INSERT INTO tblTest(title,articledate) VALUES ('this is a test', '" & articledate & "')"

I get an error.

If I copy my files up to my live webserver (IIS5)

The insert works fine on sql server and through the front end.

Not sure why this happens - maybe a setting in IIS 6?
Anthony PerkinsCommented:
As has been pointed out, when using SQL Server always, always use an unambiguous date format such as yyyy-mm-dd hh:mm:ss. Don't depend on the vagaries of configurations on different servers using IIS and / or SQL Server.  It is simply not worth wasiting time on it.
DragonlairdCommented:
Personally, I've always preferred using a function to convert a supplied date into a format useable on any DB, regardless of international settings on the server.

      Function SQLDate(sDate)
            ' Format a date/time value to use in SQL for any DB format
            Dim sDate2
            sDate2 = Day(sDate) & "-" & Mnth(Month(sDate)) & "-" & Year(sDate) & " " 
            If Hour(sDate) < 10 Then sDate2 = sDate2 & "0"
            sDate2 = sDate2 & Hour(sDate) & ":"
            If Minute(sDate) < 10 Then sDate2 = sDate2 & "0"
            sDate2 = sDate2 & Minute(sDate) & ":"
            If Second(sDate) < 10 Then sDate2 = sDate2 & "0"
            sDate2 = sDate2 & Second(sDate)
            SQLDate = sDate2
      End Function
      Function Mnth(iMnth)
            Mnth = "Unknown"
            Select Case iMnth
                  Case 1
                        Mnth = "Jan"
                  Case 2
                        Mnth = "Feb"
                  Case 3
                        Mnth = "Mar"
                  Case 4
                        Mnth = "Apr"
                  Case 5
                        Mnth = "May"
                  Case 6
                        Mnth = "Jun"
                  Case 7
                        Mnth = "Jul"
                  Case 8
                        Mnth = "Aug"
                  Case 9
                        Mnth = "Sep"
                  Case 10
                        Mnth = "Oct"
                  Case 11
                        Mnth = "Nov"
                  Case 12
                        Mnth = "Dec"
            End Select
      End Function

Use the above in your code as follows...

strSQL = "INSERT INTO tblTest(title,articledate) VALUES ('this is a test', '" & SQLDate(MyDate) & "')"

The function returns the date in the following format 'dd-MMM-yyyy HH:mm:ss' which cannot be confused as the MMM value is in text format, not a numeric month.

Hope this helps...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
>>Personally, I've always preferred using a function to convert a supplied date into a format useable on any DB, regardless of international settings on the server.<<
Absolutely.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.