Error in converting from datediff(,,) to Varchar()

Question

Error in converting from datediff(,,) to Varchar()

amitabhssinha

SSC-Addicted

Points: 481
More actions
December 31, 2009 at 2:58 am

#228863

While converting from Datediff(second,date1,date2) to varchar() I am getting following error :
"Syntax error converting the nvarchar value '95 hr' to a column of data type int."
Anyone can please show me path.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

amitabhssinha (12/31/2009)
While converting from Datediff(second,date1,date2) to varchar() I am getting following error :
"Syntax error converting the nvarchar value '95 hr' to a column of data type int."
Anyone can please show me path.

Not really. Unless you show us what you've tried and what the values (including data type) are for date1 and date2 values...

So, please post your code and samples.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

amitabhssinha SSC-Addicted Points: 481 More actions · Answer 2

This is part of my query:

(CASE

WHEN (DATEDIFF(second,Actual.ActualDepartTime,Schedule.SchdDepartTime)/60) >=60

THEN Convert(nvarchar(10),(DATEDIFF(second,Actual.ActualDepartTime,Schedule.SchdDepartTime)/60)/60)+' '+'hr'

Else (DATEDIFF(second,Actual.ActualDepartTime,Schedule.SchdDepartTime)/60) +' '+'min.'

END) AS DeptEarly

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 3

amitabhssinha (12/31/2009)
This is part of my query:
(CASE
WHEN (DATEDIFF(second,Actual.ActualDepartTime,Schedule.SchdDepartTime)/60) >=60
THEN Convert(nvarchar(10),(DATEDIFF(second,Actual.ActualDepartTime,Schedule.SchdDepartTime)/60)/60)+' '+'hr'
Else (DATEDIFF(second,Actual.ActualDepartTime,Schedule.SchdDepartTime)/60) +' '+'min.'
END) AS DeptEarly

There's an error in your ELSE condition

DROP TABLE #Actual

DROP TABLE #Schedule

CREATE TABLE #Actual (ActualID INT, ActualDepartTime DATETIME)

CREATE TABLE #Schedule (ScheduleID INT, ActualID INT, SchdDepartTime DATETIME)

INSERT INTO #Actual (ActualID, ActualDepartTime)

SELECT 1, '2009-12-28 10:28:40.943' UNION ALL

SELECT 2, '2009-12-29 10:28:40.943' UNION ALL

SELECT 3, '2009-12-30 10:28:40.943' UNION ALL

SELECT 4, '2009-12-31 10:28:40.943'

INSERT INTO #Schedule (ScheduleID, ActualID, SchdDepartTime)

SELECT 10, 1, '2009-12-28 11:00:40.943' UNION ALL

SELECT 11, 2, '2009-12-29 12:00:40.943' UNION ALL

SELECT 12, 3, '2009-12-30 13:00:40.943' UNION ALL

SELECT 13, 4, '2009-12-31 14:00:40.943'

SELECT

(DATEDIFF(second,a.ActualDepartTime,s.SchdDepartTime)/60),

Convert(nvarchar(10),(DATEDIFF(second,a.ActualDepartTime,s.SchdDepartTime)/60)/60)+' '+'hr',

--(DATEDIFF(second,a.ActualDepartTime,s.SchdDepartTime)/60) +' '+'min.' -- < DATEDIFF(second returns a number

Convert(nvarchar(10),(DATEDIFF(second,a.ActualDepartTime,s.SchdDepartTime)/60)) +' '+'min.' -- < so CAST as a character type

FROM #Actual a

INNER JOIN #Schedule s ON s.ActualID = a.ActualID

Cheers

ChrisM@home

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

LutzM SSC Guru Points: 107049 More actions · Answer 4

amitabhssinha (12/31/2009)
This is part of my query:
(CASE
WHEN (DATEDIFF(second,Actual.ActualDepartTime,Schedule.SchdDepartTime)/60) >=60
THEN Convert(nvarchar(10),(DATEDIFF(second,Actual.ActualDepartTime,Schedule.SchdDepartTime)/60)/60)+' '+'hr'
Else (DATEDIFF(second,Actual.ActualDepartTime,Schedule.SchdDepartTime)/60) +' '+'min.'
END) AS DeptEarly

Yep, you're "trapped" by SQL Servers concept to define the data type of the result.

In a CASE statement the result will be of the same data type as the one with the highest precedence for each WITH clause.

So, your first check will result in a varchar value, but the second one is "translated" into an integer.

Since your data will go the "first path", you're getting the error you described.

If you'd use a value that would use the "second path" it would result in

Msg 245, Level 16, State 1, Line 11

Conversion failed when converting the varchar value 'min.' to data type int.

To make it short:

Each CASE statement need to have the same data type or at least must be able to be converted into the one with the highest precedence.

Try

(CASE

WHEN (DATEDIFF(second,Actual.ActualDepartTime,Schedule.SchdDepartTime)/60) >=60

THEN Convert(nvarchar(10),(DATEDIFF(second,Actual.ActualDepartTime,Schedule.SchdDepartTime)/60)/60)+' '+'hr'

Else Convert(nvarchar(10),(DATEDIFF(second,Actual.ActualDepartTime,Schedule.SchdDepartTime)/60)) +' '+'min.'

END) AS DeptEarly

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]