Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Date time conversion error Expand / Collapse
Author
Message
Posted Thursday, February 21, 2013 8:40 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, February 24, 2013 6:59 AM
Points: 192, Visits: 247
Hi,

I have a stored proc which inserts date time. It inserts successfully. But 2 PM afternoon, it gives me date time converion error. The next day it works fine. But daily after 2 PM, it gives error converting varchar to date time error

The weird thing is its showing this error only in one of the server, which is live server. Whereas it works fine else where in other servers.

The server level setting is US English, the logins used are US English.
Any idea where to look pls?
Post #1422855
Posted Thursday, February 21, 2013 8:59 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:19 PM
Points: 23,286, Visits: 32,012
Can you show us some of the data that is failing?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1422858
Posted Friday, February 22, 2013 12:44 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, February 24, 2013 6:59 AM
Points: 192, Visits: 247
15/02/2013 04:25:57
21/02/2013 02:25:57

these are non working
Post #1422897
Posted Friday, February 22, 2013 2:05 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
can you plz send the script the you are using for this?


_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1422921
Posted Friday, February 22, 2013 2:50 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 30, 2014 3:50 AM
Points: 99, Visits: 176
Please check the below points too:

1. what is you DB server time zone & web server time zone
2. Are you inserting datetime variable or a varchar variable data
3. As you are saying that you got error while inserting data, I think you are using varchar string as input. So, please check the server time zone (ddmmyyy) or (mmddyyy).

I hope this can help.
Post #1422939
Posted Friday, February 22, 2013 6:50 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:19 PM
Points: 23,286, Visits: 32,012
cmrhema (2/22/2013)
15/02/2013 04:25:57
21/02/2013 02:25:57

these are non working


These are invalid dates for US English settings.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1423033
Posted Saturday, February 23, 2013 6:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, February 24, 2013 6:59 AM
Points: 192, Visits: 247
Thanks all for the inputs.
Found out the reason.
We have created a different service account for various applications.
And there is one service account that was used across three applications in three different servers.
And in one of the server the language setting was different
This was the issue, we have added the "language" in our connection string, to override the differences.
Thanks all
Post #1423346
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse