July 24, 2018 at 9:35 am
Hello, so I'm trying to get the difference in MINUTES between two columns.
One column is a DATETIME column while the other is a VARCHAR column that contains Military time values.
When I do use: DATEDIFF(MINUTE,CAST(t.StartTime as TIME), CAST(t.StopDateTime as TIME)) everything works great until I run into multiple days.
Unfortunately, the StartTime is only time, so if I have 23:51(very close to switching to the next day) and a StopDateTime of 2018-07-18 00:01:26....I'd like to somehow have the code know that the StartTime is greater than the StopTime and calculate the correct amount of MINUTES.
My example code is as follows:
CREATE TABLE #TEST (
StopDateTime DATETIME
,StartTime VARCHAR(40)
)
INSERT INTO #TEST
SELECT
StopDateTime = '2018-07-16 00:01:26.353'
,StartTime = '23:51'
SELECT
t.*
,DATEDIFF(MINUTE,CAST(t.StartTime AS time), CAST(t.StopDateTime AS time)) AS DiffInMinutes
FROM #TEST t
By running that test code, you'll see the DiffInMinutes is -1430, when I really think it should equate to 10 minutes(I don't care about seconds, milliseconds).
Does anyone have any suggestions on how to tackle that problem?
July 24, 2018 at 9:44 am
cor_dog2 - Tuesday, July 24, 2018 9:35 AMHello, so I'm trying to get the difference in MINUTES between two columns.
One column is a DATETIME column while the other is a VARCHAR column that contains Military time values.
When I do use: DATEDIFF(MINUTE,CAST(t.StartTime as TIME), CAST(t.StopDateTime as TIME)) everything works great until I run into multiple days.
Unfortunately, the StartTime is only time, so if I have 23:51(very close to switching to the next day) and a StopDateTime of 2018-07-18 00:01:26....I'd like to somehow have the code know that the StartTime is greater than the StopTime and calculate the correct amount of MINUTES.My example code is as follows:
CREATE TABLE #TEST (
StopDateTime DATETIME
,StartTime VARCHAR(40)
)
INSERT INTO #TEST
SELECT
StopDateTime = '2018-07-16 00:01:26.353'
,StartTime = '23:51'
SELECT
t.*
,DATEDIFF(MINUTE,CAST(t.StartTime AS time), CAST(t.StopDateTime AS time)) AS DiffInMinutes
FROM #TEST t
By running that test code, you'll see the DiffInMinutes is -1430, when I really think it should equate to 10 minutes(I don't care about seconds, milliseconds).
Does anyone have any suggestions on how to tackle that problem?
You could just add one day when the start time is greater than the end time.
CREATE TABLE #TEST (
StopDateTime DATETIME
,StartTime VARCHAR(40)
)
INSERT INTO #TEST
SELECT
StopDateTime = '2018-07-16 00:01:26.353'
,StartTime = '23:51'
SELECT
t.*
,DATEDIFF(MINUTE,CAST(t.StartTime AS time), CAST(t.StopDateTime AS time))
+ CASE WHEN CAST(t.StartTime AS time) <= CAST(t.StopDateTime AS time)
THEN 0
ELSE 60*24 END AS DiffInMinutes
FROM #TEST t;
July 24, 2018 at 9:54 am
you can check to see if the start time > end time and subtract 1 from the date of the end time, but that only assumes that it will be the previous day if the start time > end time and only accounts for more than 1 day.
drop table if exists #Test
CREATE TABLE #TEST (
StopDateTime DATETIME
,StartTime VARCHAR(40)
)
INSERT INTO #TEST
SELECT
StopDateTime = '2018-07-16 00:01:26.353'
,StartTime = '23:51'
union all
SELECT
StopDateTime = '2018-07-16 13:05:26.353'
,StartTime = '10:51'
SELECT
t.*
,cast(t.StopDateTime as time)
, cast(t.StartTime as Time)
, case when cast(t.StopDateTime as time) < cast(t.StartTime as Time)
then DATEDIFF(MINUTE,cast(cast(DateAdd(day, -1, cast(t.StopDateTime as Date)) as datetime) + ' ' + t.StartTime as datetime), t.StopDateTime )
else DATEDIFF(MINUTE,CAST(t.StartTime AS time), CAST(t.StopDateTime AS time))
end
FROM #TEST t
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 24, 2018 at 9:55 am
Oh, I was trying to do that and so close, but kept getting conversion errors, etc or incorrect minutes with everything I was trying.
This works perfectly though!! Thank you so much Luis!! Really appreciate this very much.
July 24, 2018 at 9:57 am
Thank you Mike too. I haven't tried yours yet, but I imagine it would work as well. 🙂
July 25, 2018 at 12:39 pm
Just another way to do it:CREATE TABLE #TEST (
StopDateTime datetime,
StartTime time
);
INSERT INTO #TEST (StopDateTime, StartTime)
SELECT
StopDateTime = '2018-07-16 00:01:26.353',
StartTime = '23:51';
SELECT
T.*,
DATEDIFF(minute,
CASE
WHEN T.StartTime > CONVERT(time, T.StopDateTime) THEN TTY.StartDateTimeYesterday
ELSE TTY.StartDateTime
END,
T.StopDateTime) AS DiffInMinutes
FROM #TEST AS T
CROSS APPLY (SELECT CONVERT(datetime, CONVERT(date, T.StopDateTime)) AS StopDate) AS SD
CROSS APPLY (
SELECT
DATEADD(minute,
DATEPART(minute, T.StartTime),
DATEADD(hour,
DATEPART(hour, T.StartTime),
CONVERT(datetime, CONVERT(date, SD.StopDate))
)
) AS StartDateTime,
DATEADD(minute,
DATEPART(minute, T.StartTime),
DATEADD(hour,
DATEPART(hour, T.StartTime),
CONVERT(datetime, CONVERT(date, DATEADD(day, -1, SD.StopDate)))
)
) AS StartDateTimeYesterday
) AS TTY;
DROP TABLE #TEST;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 25, 2018 at 3:28 pm
cor_dog2 - Tuesday, July 24, 2018 9:35 AM
Does anyone have any suggestions on how to tackle that problem?
My first suggestion is to go to the University of Arizona website and download the book on temporal queries in SQL by Rick Snodgrass. It's in a PDF and it's free.
Next, don't mix data types like this! A 40 character string of Unicode characters is not a temporal value. Your input tier in your database should be putting temporal values in here, which would be what ANSI calls timestamps and what Microsoft calls DATETIME2(0). Instead of a tiered architecture you're trying to find some kludge to convert your invented data type (Military time? I hate to tell you this but the Army uses ISO 8601 temporal display formats these days). The ability to scrub data as it comes into the database is one of the major advantages of tiered architecture's in modern programming.
Please post DDL and follow ANSI/ISO standards when asking for help.
July 26, 2018 at 5:16 am
cor_dog2 - Tuesday, July 24, 2018 9:35 AMHello, so I'm trying to get the difference in MINUTES between two columns.
//snip//
Unfortunately, the StartTime is only time, so if I have 23:51(very close to switching to the next day) and a StopDateTime of 2018-07-18 00:01:26....I'd like to somehow have the code know that the StartTime is greater than the StopTime and calculate the correct amount of MINUTES.
//snip//
How do you know that if the start time is 23:51 it means 23:51 on the day before the stop date time? Could it be 2 or 3 days before the stop?
Perhaps in your application you know that duration is less than one full day. Do you know that will always be so?
July 27, 2018 at 12:09 pm
gvoshol 73146 - Thursday, July 26, 2018 5:16 AMcor_dog2 - Tuesday, July 24, 2018 9:35 AMHow do you know that if the start time is 23:51 it means 23:51 on the day before the stop date time? Could it be 2 or 3 days before the stop?
Perhaps in your application you know that duration is less than one full day. Do you know that will always be so?
You're completely correct. This is one of the many problems with inventing your own datatypes, and then using character strings to represent them. Back before we had temporal datatypes, we used to do this sort of crap in COBOL (that language only had strings) and then hope that somebody could write a whole function library for invented datatypes.
One of my favorite T-shirts, which illustrates the problems of mixed data types, reads, "on a scale from 1 to 10, what color is your favorite letter of the alphabet? :crazy:"
Please post DDL and follow ANSI/ISO standards when asking for help.
July 27, 2018 at 12:14 pm
jcelko212 32090 - Friday, July 27, 2018 12:09 PMgvoshol 73146 - Thursday, July 26, 2018 5:16 AMcor_dog2 - Tuesday, July 24, 2018 9:35 AMHow do you know that if the start time is 23:51 it means 23:51 on the day before the stop date time? Could it be 2 or 3 days before the stop?
Perhaps in your application you know that duration is less than one full day. Do you know that will always be so?You're completely correct. This is one of the many problems with inventing your own datatypes, and then using character strings to represent them. Back before we had temporal datatypes, we used to do this sort of crap in COBOL (that language only had strings) and then hope that somebody could write a whole function library for invented datatypes.
One of my favorite T-shirts, which illustrates the problems of mixed data types, reads, "on a scale from 1 to 10, what color is your favorite letter of the alphabet? :crazy:"
I really am tired of your bashing on COBOL and COBOL programmers. Just because you had problems using it doesn't mean the rest of us did.
July 28, 2018 at 3:11 pm
cor_dog2 - Tuesday, July 24, 2018 9:35 AM
I really am tired of your bashing on COBOL and COBOL programmers. Just because you had problems using it doesn't mean the rest of us did.
I am not bashing COBOL programmers or COBOL. After decades of teaching computer science and programming languages, I found out that people seldom make random mistakes. Just like natural languages, when people learn a new programming language, they mimic their original language. This also applies to the people that taught them the language; this is why I had a cousin that had a slight Slavic accent, even though she was third-generation American. If you don't know the reason people make mistakes, it's much, much harder to teach them the right way.
For example, a common mistake in T SQL is using != Instead of <>. This tells us that, like the people that wrote the first T-SQL compilers, you are probably a programmer whose first experience was in the C family of languages. If the compiler writers had been PL/I programmers, this might have been ~=, or .NE. if they used Fortran.
COBOL still happens to be one of the most popular language on earth, with over 90% of commercial programs at banks, financial institutes, wholesale and retail programs written in it. It should come as no surprise that when people begin writing SQL, we are influenced heavily by COBOL. You're literally swimming in an environment where this is the most fundamental language and use.
Please post DDL and follow ANSI/ISO standards when asking for help.
July 30, 2018 at 2:34 am
jcelko212 32090 - Friday, July 27, 2018 12:09 PMgvoshol 73146 - Thursday, July 26, 2018 5:16 AMcor_dog2 - Tuesday, July 24, 2018 9:35 AMHow do you know that if the start time is 23:51 it means 23:51 on the day before the stop date time? Could it be 2 or 3 days before the stop?
Perhaps in your application you know that duration is less than one full day. Do you know that will always be so?You're completely correct. This is one of the many problems with inventing your own datatypes, and then using character strings to represent them. Back before we had temporal datatypes, we used to do this sort of crap in COBOL (that language only had strings) and then hope that somebody could write a whole function library for invented datatypes.
One of my favorite T-shirts, which illustrates the problems of mixed data types, reads, "on a scale from 1 to 10, what color is your favorite letter of the alphabet? :crazy:"
We just had a whole set of libraries written for doing things like date manipulation in COBOL.
However, like Joe says some people never change and we had an old COBOLer who never used the DATE datatype in Oracle, just NUMBER(8).
August 2, 2018 at 12:04 am
Here the SQL solutions presented are just patchwork as this is really a design problem. Now you probably cannot go and change things in a table or a ETL process to make the column type similar. Just know that if a system went down and data was imported say two days later, that the end time date portion may default to the current day, and that may be incorrect. So the start time portion could still differ by n minutes but now you dont know when it started or ended, only minutes between them. Not sure also if the start date could lag by more than just the prior day as you have to evaluate the logic creating the writes and see if there are holes in there that can be exploited by some use case.
----------------------------------------------------
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply