T-SQL help on datetime

Question

Post reply

T-SQL help on datetime

S7

Mr or Mrs. 500

Points: 577
More actions
January 28, 2014 at 12:11 pm

#303510

Getting "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value." error when running the below script:-
delete from table
WHERE CONVERT(datetime, cast([year] as varchar(4)) + '-' + cast([month] as varchar(2)) + '-' + cast([day] as varchar(2))) = '?';
The table without the where clause output is:-
Year MonthDay
2001 914
2001 915
Any sugesstion on how to twick this query. Thanks.
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]

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

I can't reproduce your error. What are you using instead of '?'?

Can you post DDL of the table?

Here's what I used to test.

WITH SomeTable([year],[month],[day]) AS(

SELECT 2001,9,14 UNION ALL SELECT 2001,9,15)

SELECT *,CONVERT(datetime, cast([year] as varchar(4))

+ '-' + cast([month] as varchar(2))

+ '-' + cast([day] as varchar(2)) )

FROM SomeTable

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

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 2

As Luis pointed out the conversion works fine with the sample you have. Maybe there is an instance of data that doesn't contain a valid entry or simply IS out of range:

Example:

2014, 2, 29

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman

ScottPletcher SSC Guru Points: 101126 More actions · Answer 3

Don't convert to a datetime, just compare it as characters. Invalid data may still get through, but you can't easily at all correct invalid data on the fly in the query anyway:

WHERE

cast([year] as varchar(4)) + '-' + right('0' + cast([month] as varchar(2)), 2) + '-' + right('0' + cast([day] as varchar(2)), 2) = '?';

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

ScottPletcher (1/28/2014)
Don't convert to a datetime, just compare it as characters. Invalid data may still get through, but you can't easily at all correct invalid data on the fly in the query anyway:
WHERE
cast([year] as varchar(4)) + '-' + right('0' + cast([month] as varchar(2)), 2) + '-' + right('0' + cast([day] as varchar(2)), 2) = '?';

In that case, she could even compare the columns without concatenation or data conversion.

WHERE [year] = YEAR(@Date)

AND [month] = MONTH(@Date)

AND [day] = DAY(@Date)

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

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

I would go one step further and say don't do anything to your year, month and day columns in the WHERE clause.

Use something like this instead:

WHERE year=? AND month=? AND day=?

and pass in the three parameters separately (this is an ODBC query I take it from the use of ? for a parameter...).

Whatever your method of calling it, you will get better results from not using functions on those columns as long as you have an index that covers them.

The error will possible be coming from the '?' parameter, not the table's data by the way.

MM

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

ScottPletcher SSC Guru Points: 101126 More actions · Answer 6

Luis Cazares (1/28/2014)
ScottPletcher (1/28/2014)
Don't convert to a datetime, just compare it as characters. Invalid data may still get through, but you can't easily at all correct invalid data on the fly in the query anyway:
WHERE
cast([year] as varchar(4)) + '-' + right('0' + cast([month] as varchar(2)), 2) + '-' + right('0' + cast([day] as varchar(2)), 2) = '?';
In that case, she could even compare the columns without concatenation or data conversion.
WHERE [year] = YEAR(@Date)
AND [month] = MONTH(@Date)
AND [day] = DAY(@Date)

Looked to me like it was being compared to a value being passed in, as represented by the ? placeholder. I didn't assume the input was datetime either -- if it's coming in as character, trying to convert it to a date could cause the same issue.

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

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 7

Debora (1/28/2014)
Getting "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value." error when running the below script:-
delete from table
WHERE CONVERT(datetime, cast([year] as varchar(4)) + '-' + cast([month] as varchar(2)) + '-' + cast([day] as varchar(2))) = '?';
The table without the where clause output is:-
Year MonthDay
2001 914
2001 915
Any sugesstion on how to twick this query. Thanks.

The error is not in casting the date part strings to DATETIME, but in the implicit casting of the string '?' to DATETIME to be able to evaluate the = condition.

WHERE

CONVERT(datetime, cast([year] as varchar(4)) + '-' +

cast([month] as varchar(2)) + '-' + cast([day] as varchar(2))) = '?';

ScottPletcher SSC Guru Points: 101126 More actions · Answer 8

Michael Valentine Jones (1/29/2014)
Debora (1/28/2014)
Getting "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value." error when running the below script:-
delete from table
WHERE CONVERT(datetime, cast([year] as varchar(4)) + '-' + cast([month] as varchar(2)) + '-' + cast([day] as varchar(2))) = '?';
The table without the where clause output is:-
Year MonthDay
2001 914
2001 915
Any sugesstion on how to twick this query. Thanks.
The error is not in casting the date part strings to DATETIME, but in the implicit casting of the string '?' to DATETIME to be able to evaluate the = condition.
WHERE
CONVERT(datetime, cast([year] as varchar(4)) + '-' +
cast([month] as varchar(2)) + '-' + cast([day] as varchar(2))) = '?';

Perhaps ... it depends on the specific date settings.

If "SET DATEFORMAT dmy" is in effect (implicitly or explicitly), then '2001-9-14' will cause that conversion error.

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