Datediff function

[Ambertje]

Hi Chris, I need my dates as string because I'm working with a Data warehouse DB.

, CAST(Replace(convert(date, [START_DATE], 101), '-', '') as numeric(8,0)) AS [START_DATE]

, CAST(dbo.LPAD(dbo.CONVERT_TIME_TO_NUMERIC(SUBSTRING(dbo.arKD.START_TIME,1,8)), 6, 0)AS nvarchar(6)) AS START_TIME

, CAST(Replace(convert(date, END_DATE, 101), '-', '') as numeric(8,0)) AS END_DATE

, CAST(dbo.LPAD(dbo.CONVERT_TIME_TO_NUMERIC(SUBSTRING(dbo.arKD.END_TIME,1,8)), 6, 0)AS nvarchar(6)) AS END_TIME

--DATEDIFF(interval, Starting_date datetime, ending_date datetime)
,Datediff(SECOND,
convert(datetime, [START_DATE] + ' ' + [START_TIME]),
convert(datetime, [END_DATE] + ' ' + [END_TIME])) AS M_Time_Worked

This is actual code where I Cast all dates to nvarchar. So how can I change this code in a way that I can set the Start_Date (when empty) to the value of End_Date and Start_Time to the value of End_Time when empty? I'm not an experienced developer, I'm still learning and hope you can learn me something new. Kind regards, Ambertje

[Chris Quinn]

,Datediff(SECOND,
convert(datetime, [START_DATE] + ' ' + [START_TIME]),
convert(datetime, CASE WHEN RTRIM([END_DATE]) = '' THEN [START_DATE] ELSE [END_DATE] END + ' ' + CASE WHEN RTRIM([END_TIME]) = '' THEN [START_TIME] ELSE [END_TIME] END)) AS M_Time_Worked

========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================

[Ambertje]

Still an error:

The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

[GuyThiebaut]

I would step back and bit and ask the question - if the start date is null is this calculation valid? What does a null start date mean - it means that the start date is unknown. So my question is this - if the start date is unknown what is it you would like to do?

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

[PIEBALDconsult]

Are you looking for ISNULL(START_DATE , END_DATE) ?

[Jorgen Andersson]

Ambertje wrote:

I need my dates as string because I'm working with a Data warehouse DB.

Why would the date format be a problem in a Data Warehouse?

Wrong is evil and must be defeated. - Jeff Ello

[Ambertje]

Good morning to you all, It's Start_Date and Start_Time that can be empty. So I think ISNULL(Start_Date, Start_Time) is what I need. How can I implement this in my code?

,Datediff(SECOND,
convert(datetime, [START_DATE] + ' ' + [START_TIME]),
convert(datetime, [END_DATE] + ' ' + END_TIME])) AS M_Time_Worked

Kind regards, Ambertje

[Ambertje]

You are correct but when I run this statement I get no values. So I went on to try some more ways to make it work and this statement works:

, CASE WHEN [START_DATE] IS NULL OR [START_DATE] = '' THEN
CAST(Replace(convert(date, END_DATE, 101), '-', '') as numeric(8,0)) ELSE
CAST(Replace(convert(date, [START_DATE], 101), '-', '') as numeric(8,0)) END AS [START_DATE]

But I can't integrate it in my DateDiff statement, can you help me please? Ambertje

[Corporal Agarn]

Ambertje wrote:

So I think ISNULL(Start_Date, Start_Time) is what I need.

That is not how ISNULL works. You would need something like

ISNULL(Start_Date, EndDate) + ' ' + ISNULL(Start_Time, Endtime)

[Ambertje]

You are correct, but still it wont work like that. Something was missing and after analasys I saw that the Start_Date field wasn't NULL but just empty. So this is what I changed to make it work:

,Datediff(SECOND,
convert(datetime, ISNULL( NULLIF([START_DATE], '' ) , END_DATE) + ' ' + ISNULL( NULLIF([START_TIME], '' ) , END_TIME)),
convert(datetime, [END_DATE] + ' ' + [END_TIME])) AS M_Time_Worked

A big thank's to all for helping me and the responses where I learned from. Kind regards, Ambertje