datediff with numeric and datetime field

Question

datediff with numeric and datetime field

mm-1009269

SSCommitted

Points: 1559
More actions
July 10, 2009 at 2:33 pm

#136162

Hello,
I have a rather simple question but I'm a bit stuck...
I want to perform a datediff between a datetime field and another field which is numeric but displays as a date / time.
This is my script and as you can see I'm trying to do my datediff below.
SELECT DISTINCT
SAP_CRM_P1_Calls.[Transaction ID]
, SAP_CRM_P1_Calls.NAME1
, SAP_CRM_P1_Calls.[Downtime Start]
, SAP_CRM_P1_Calls.ED_TST_TO
, SAP_CRM_P1_Calls.[Hours Elapsed]
, CRM_Orders.PRIORITY_DESCRIPTION
, CRM_Orders.CREATED_AT
, CRM_StatusCodes.UDATE
, CRM_StatusCodes.USER_STATUS
--, datediff(hour,SAP_CRM_P1_Calls.ED_TST_TO, SAP_CRM_P1_Calls.[Downtime Start]) AS [Service Order Duration (hours)]
FROM SAP_CRM_P1_Calls INNER JOIN
CRM_Orders ON SAP_CRM_P1_Calls.[Transaction ID] = CRM_Orders.TRANSACTION_ID INNER JOIN
CRM_StatusCodes ON CRM_Orders.TRANSACTION_ID = CRM_StatusCodes.TRANSACTION_ID
WHERE
CRM_StatusCodes.USER_STATUS = 'Complete'
AND CRM_Orders.PRIORITY_DESCRIPTION = 'P1 - Down'
AND SAP_CRM_P1_Calls.NAME1 <> ' '
AND CRM_StatusCodes.UDATE LIKE ('200906%')
The data type for SAP_CRM_P1_Calls.ED_TST_TO is numeric(15,0) (example: 20090616052600) and
the datatype for SAP_CRM_P1_Calls.[Downtime Start] is datetime (example: 2009-03-16 22:27:20.000)
Could somebody please help me?
Thank you very much in advance!!
Michelle

Viewing 7 posts - 1 through 6 (of 6 total)

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

DATEDIFF only accepts values of type datetime. You need to convert your numeric value to datetime using the CONVERT function. Give it a shot and post additional questions if you have problems.

John Rowan

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

mm-1009269 SSCommitted Points: 1559 More actions · Answer 2

Sorry, John. I should've mentioned that I was trying to convert it to datetime already 'cuz I know that datediff only accepts dates.

I have no problem convert from varchar, but numeric gives me a little trouble.

I think I have to do something like this:

cast(stuff(stuff(stuff(cast(SAP_CRM_P1_Calls.ED_TST_TO as varchar(17)), 9, 0, ' '), 12, 0, ':'), 15, 0, ':') as datetime)

But I'm not sure how to incorporate this with a datediff funtion.

Thanks

Michelle 🙂

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 3

Something like:DATEDIFF

(

HOUR,

cast(stuff(stuff(stuff(cast(SAP_CRM_P1_Calls.ED_TST_TO as varchar(17)), 9, 0, ' '), 12, 0, ':'), 15, 0, ':') as datetime),

SAP_CRM_P1_Calls.[Downtime Start]

) AS [Service Order Duration (hours)]

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 4

DECLARE@num NUMERIC(15, 0),

@dt DATETIME

SELECT@num = 20090616052600,

@dt = '20090316 22:27:20'

SELECTDATEDIFF(HOUR, '20090316 22:27:20', '20090616 05:26:00'),

@num,

@dt,

DATEDIFF(HOUR, @dt, STUFF(LEFT(@num, 10), 9, 0 ,' ') + ':0')

N 56°04'39.16"
E 12°55'05.25"

John Rowan SSC Guru Points: 56440 More actions · Answer 5

Peso (7/10/2009)
DECLARE@num NUMERIC(15, 0), @dt DATETIME SELECT@num = 20090616052600, @dt = '20090316 22:27:20' SELECTDATEDIFF(HOUR, '20090316 22:27:20', '20090616 05:26:00'), @num, @dt, DATEDIFF(HOUR, @dt, STUFF(LEFT(@num, 10), 9, 0 ,' ') + ':0')

Yep, that'll do. I second Peso's solution. Very simple and clean.

John Rowan

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

mm-1009269 SSCommitted Points: 1559 More actions · Answer 6

mm-1009269

SSCommitted

Points: 1559

July 10, 2009 at 3:11 pm

#1023374

Thanks guys! It works!

Michelle 😀