Datediff function
-
Sorry Chris, Same error as with my query. This is an example of such an record with empty values START_DATE| START_TIME| END_DATE | END_TIME | | 01/18/2015| 20:12:52 I'm testing with this query:
declare @t table(START_DATE varchar(10), END_TIME_1 varchar(8), START_TIME_1 varchar(8), END_DATE varchar (10))
insert @t values('', '20:12:52', '', '01/18/2015')select Datediff(SECOND,
convert(datetime, [START_DATE] + ' ' + [START_TIME_1]),
convert(datetime, [END_DATE] + ' ' + [END_TIME_1]))
from @tThat is because you are storing empty strings, not null values. You also have two empty times, which wil cause problems with the query. You also should not store dates and times as strings - use the DateTime column type provided to hold them properly.
declare @t table(START_DATE DateTime, END_DATE DateTime)
insert @t values ('01-jan-2015 20:12:52', Null)select Datediff(SECOND,[START_DATE], ISNULL([END_DATE],[START_DATE]))
from @t========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================
-
That is because you are storing empty strings, not null values. You also have two empty times, which wil cause problems with the query. You also should not store dates and times as strings - use the DateTime column type provided to hold them properly.
declare @t table(START_DATE DateTime, END_DATE DateTime)
insert @t values ('01-jan-2015 20:12:52', Null)select Datediff(SECOND,[START_DATE], ISNULL([END_DATE],[START_DATE]))
from @t========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================
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_WorkedThis 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
-
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_WorkedThis 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
,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. =========================================================
-
,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. =========================================================
-
Hello, I want to calculate the difference between 2 dates and times in seconds.
, Datediff(SECOND,
convert(datetime, [START_DATE] + ' ' + [START_TIME]),
convert(datetime, [END_DATE] + ' ' + [END_TIME])) AS M_Time_WorkedNog problem with the function but when I come across a record where the Start_Date is empty then I get an overflow (I know that you can calculate max 68 years worth of seconds) I've tried to use a Case statement but it wont work like this:
, CASE WHEN START_DATE IS NULL THEN START_DATE = END_DATE
ELSE
Datediff(SECOND,
convert(datetime, [START_DATE] + ' ' + [START_TIME]),
convert(datetime, [END_DATE] + ' ' + [END_TIME])) END AS M_Time_WorkedDoes anyone know how I can handle empty values for a DateDiff() please? Kind regards, Ambertje
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
-
Hello, I want to calculate the difference between 2 dates and times in seconds.
, Datediff(SECOND,
convert(datetime, [START_DATE] + ' ' + [START_TIME]),
convert(datetime, [END_DATE] + ' ' + [END_TIME])) AS M_Time_WorkedNog problem with the function but when I come across a record where the Start_Date is empty then I get an overflow (I know that you can calculate max 68 years worth of seconds) I've tried to use a Case statement but it wont work like this:
, CASE WHEN START_DATE IS NULL THEN START_DATE = END_DATE
ELSE
Datediff(SECOND,
convert(datetime, [START_DATE] + ' ' + [START_TIME]),
convert(datetime, [END_DATE] + ' ' + [END_TIME])) END AS M_Time_WorkedDoes anyone know how I can handle empty values for a DateDiff() please? Kind regards, Ambertje
Are you looking for
ISNULL(START_DATE , END_DATE)? -
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_WorkedThis 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
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
-
Are you looking for
ISNULL(START_DATE , END_DATE)?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_WorkedKind regards, Ambertje
-
Are you looking for
ISNULL(START_DATE , END_DATE)?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
-
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_WorkedKind regards, Ambertje
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 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)
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_WorkedA big thank's to all for helping me and the responses where I learned from. Kind regards, Ambertje