Error converting data type varchar to datetime.

Question

Post reply

Error converting data type varchar to datetime.

sirishgreen

Old Hand

Points: 398
More actions
May 19, 2014 at 8:10 am

#288934

I have a stored procedure that works on 1 server but not on another. I quess this is a setting somewhere but I don't know where. My issue is that all of our other stored procedures work.
There are 2 datetime parameters used inside the stored procedure @start and @end, but it doesn't seem like it is getting into the execution, but failing with the call.
exec ipf_sp_Diary_ABSampleReviews @start='2014-04-26 00:00:00',@end='2014-06-07 00:00:00',@bDiaryAssignedto=0,@bDiaryCreatedBy=0,@bDiaryAsset=0,@bModules=0,@bUserGroup=0,@DiarystrUser='cipfa'
If I swap the dates around '2014-26-4 00:00:00' then it works. You would think this would lead me to the solution but unfortunately not.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

rafael_si Ten Centuries Points: 1059 More actions · Answer 1

Hello,

Verify the default language selected for your login in both server. It's look like a format issue regarding the language.

Regards,

Rafael Melo - BR

Luis Cazares SSC Guru Points: 183700 More actions · Answer 2

You could try removing the dashes from your dates. SQL Server will identify the dates without problem regardless of language or dateformat configuration.

Another option is to use

SET DATEFORMAT ymd

before the call to your SP.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ScottPletcher SSC Guru Points: 101045 More actions · Answer 3

SQL always interprets format 'YYYYMMDD' correctly, regardless of SQL settings:

, @start='20140426 00:00:00',@end='20140607 00:00:00'

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

sirishgreen Old Hand Points: 398 More actions · Answer 4

Hi,

Thank you for the replies.

I have looked at the default language for the login. This was British English, so I assume I am okay there. Interestingly if I do a Select getdate() it is returned in the format I would expect.

I had read up on changing the format to without the dashes but there are thousands of stored procedures in use that don't use that format, so I would rather work out why it is not working for mine. In order to set the datetime format I guess I could call my procedure from within another stored procedure, not sure if this would work and don't see why I should have to do this.

I am finding it really frustrating that I can't stop the server from interpreting the string this way as it is not consistent with any of my settings, or even how the getdate() function is being returned.

sirishgreen Old Hand Points: 398 More actions · Answer 5

I took your advice:

these dates result in an error:

@dtStart='20140418',@dtEnd='20140418'

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Thanks

Simon

ScottPletcher SSC Guru Points: 101045 More actions · Answer 6

sirishgreen (5/23/2014)
I took your advice:
these dates result in an error:
@dtStart='20140418',@dtEnd='20140418'
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Thanks
Simon

Then the error must be somewhere else. Those values can never cause an error when going into date/datetime column.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

sirishgreen Old Hand Points: 398 More actions · Answer 7

sorry my mistake - the error occurs from SQL but only when I run it through my vb.net code. Using profiler if I execute the traced procedure it works!

Ed Wagner SSC Guru Points: 287026 More actions · Answer 8

Then you're going to need to format your dates in your vb.net code before you call the stored procedure. If the SQL works, all you have left to do is call it properly. In short, the hard part should be done.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

sirishgreen Old Hand Points: 398 More actions · Answer 9

I thought I had cracked this issue but now....

and DE_Date between

'20140906' and '20140906'

This is being read as The 9th of June by SQL server.

After I looked around I found this http://social.msdn.microsoft.com/Forums/sqlserver/en-US/7bced49f-bbb6-40af-8eb9-f24b47287dca/yyyymmdd-not-safe

"it is a well known fact that yyyymmdd is not safe"

ScottPletcher SSC Guru Points: 101045 More actions · Answer 10

sirishgreen (6/3/2014)
I thought I had cracked this issue but now....
and DE_Date between
'20140906' and '20140906'
This is being read as The 9th of June by SQL server.
After I looked around I found this http://social.msdn.microsoft.com/Forums/sqlserver/en-US/7bced49f-bbb6-40af-8eb9-f24b47287dca/yyyymmdd-not-safe
"it is a well known fact that yyyymmdd is not safe"

False! Something else is going on.

SQL Server will always treat '20140906' as Sep 6th, period!

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 11

ScottPletcher (6/3/2014)
sirishgreen (6/3/2014)
I thought I had cracked this issue but now....
and DE_Date between
'20140906' and '20140906'
This is being read as The 9th of June by SQL server.
After I looked around I found this http://social.msdn.microsoft.com/Forums/sqlserver/en-US/7bced49f-bbb6-40af-8eb9-f24b47287dca/yyyymmdd-not-safe
"it is a well known fact that yyyymmdd is not safe"
False! Something else is going on.
SQL Server will always treat '20140906' as Sep 6th, period!

I agree 100%. Something else is going on.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mister.magoo SSC-Forever Points: 47174 More actions · Answer 12

Can you post the .NET code where you prepare the SQL Command and set the parameters?

Also the header for the stored procedure - everything from CREATE PROC to AS will do so we can see the parameter definitions.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

sirishgreen Old Hand Points: 398 More actions · Answer 13

I'm sorry guys - I carried on looking at this and and found the issue. the issue was not with the stored procedure call but with my test data. Strangely, SQL server will let me copy a date from a already existing record like this "2005-12-11 08:49:14.000". but then I paste this in to an insert statement I get this "2005-11-12 08:49:14.000" in the underlying table.

so my test was flawed and I was looking in the wrong place for the problem. This did, wrong as i was, appear as a problem with my SP call.

Thanks for your help.