February 25, 2021 at 4:38 pm
The picture you posted is not a table by definition. It's got duplicate rows! You have no key or constraints. The strings you posted are not how we represent a time value in SQL (or any other ISO standard I know) Based on nothing you posted, Did you know that SQL has a TIME(n) data type? Why did you fail to use it and go for a 1950s COBOL kludge with strings instead?
CREATE TABLE Foobar (begin_time TIME(0) NOT NULL DEFAULT '00:00:00', end_time TIME(0) NOT NULL PRIMARY KEY, --- keys are not optional CHECK (begin_time < end_time),--- my guess );
I'm going to guess you want times rounded to the minute. You can either do this in the DDL or you can do it in the presentation layer. The presentation layer will probably be more accurate.
INSERT INTO Foobar (begin_time, end_time) VALUES ('08:30:00', '11:00:00'), ('12:00:00', '16:00:00'), ('00:00:00', '2359');
Heh... seriously, Joe. You have to understand when people are just posting an example. You also have to get over things like the data they posted because this junk happens all the time in real life and they've been tasked to use it. Even the job history in MSDB.dbo.sysjobhistory uses the miserable format for time that the OP is contending with and the OP can't do anything about that, either.
--Jeff Moden
February 25, 2021 at 6:29 pm
The picture you posted is not a table by definition. It's got duplicate rows! You have no key or constraints. The strings you posted are not how we represent a time value in SQL (or any other ISO standard I know) Based on nothing you posted, Did you know that SQL has a TIME(n) data type? Why did you fail to use it and go for a 1950s COBOL kludge with strings instead?
CREATE TABLE Foobar
(begin_time TIME(0) NOT NULL DEFAULT '00:00:00',
end_time TIME(0) NOT NULL PRIMARY KEY, --- keys are not optional
CHECK (begin_time < end_time),--- my guess
);
>> Heh... seriously, Joe. You have to understand when people are just posting an example. You also have to get over things like the data they posted because this junk happens all the time in real life and they've been tasked to use it. <<
Remember that I've been at this for over 30 years. The fact that bad SQL occurs "all the time." is a little like saying we need to learn to live with the plague instead of teaching people to wash their hands and flush toilets. I can't find the exact quote immediately, but is from Frank Lloyd Wright; "if you want to have good architecture, then you have to be willing to point out bad architecture". This is a principle that applies in spades with computer code.
>> Even the job history in MSDB.dbo.sysjobhistory uses the miserable format for time that the OP is contending with and the OP can't do anything about that, either. <<
Yes, vendors have a lot of bad code in their sample databases. Some of that is deliberate (a sample databases trying to be all things to all people and give examples of all possible techniques), but a lot of it is just denormalized ignorance. In this example I'm not talking that anything fancy. Why do you want to give the poster the idea that what he's doing is just fine and really good database schema? Since I make a lot of my consulting money fixing disasters, I'm the one that should be cheering for bad programmers like this!
Please post DDL and follow ANSI/ISO standards when asking for help.
February 25, 2021 at 7:49 pm
I've been in the world of data almost as long, Joe. Everything you say is true but your posts aren't changing any of that. You need to understand that the people posting aren't the cause of the problem... they're the victims. Stop treating them like criminals.
--Jeff Moden
February 25, 2021 at 8:36 pm
--===== Create and populate the test table
DROP TABLE IF EXISTS #TestTable;
CREATE TABLE #TestTable
(begin_time VARCHAR(10), end_time VARCHAR(10))
;
INSERT INTO #TestTable WITH (TABLOCK)
(begin_time,end_time)
VALUES ( '830','1100')
,('2200', '500') -- Note: spans midnight
,('1230','1200') -- Note: spans midnight
,('2359', '0') -- Note: spans midnight
,('2359','2359') -- Note: spans midnight and same time next day
,('1200','1600')
,( '0','2359')
;
--===== Solve the problem with just a minor addition to the original code.
SELECT *
,MinutesDur = IIF(begin_time/1<end_time/1,0,1440)
+ end_time/100*60+end_time%100-begin_time/100*60-begin_time%100
FROM #TestTable
;
Could also do this - not sure if it is better/worse/same:
Select *,
, MinutesDur = coalesce(nullif((end_time/100*60+end_time%100-begin_time/100*60-begin_time%100 + 1440) % 1440, 0), 1440)
From #TestTable;
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
February 25, 2021 at 8:55 pm
The picture you posted is not a table by definition. It's got duplicate rows! You have no key or constraints. The strings you posted are not how we represent a time value in SQL (or any other ISO standard I know) Based on nothing you posted, Did you know that SQL has a TIME(n) data type? Why did you fail to use it and go for a 1950s COBOL kludge with strings instead?
CREATE TABLE Foobar (begin_time TIME(0) NOT NULL DEFAULT '00:00:00', end_time TIME(0) NOT NULL PRIMARY KEY, --- keys are not optional CHECK (begin_time < end_time),--- my guess );
I'm going to guess you want times rounded to the minute. You can either do this in the DDL or you can do it in the presentation layer. The presentation layer will probably be more accurate.
INSERT INTO Foobar (begin_time, end_time) VALUES ('08:30:00', '11:00:00'), ('12:00:00', '16:00:00'), ('00:00:00', '2359');
I have read a number of your rants over the years at this site, whenever something doesn't adhere to your oh so high standards. I don't think anyone would mind if you stated your oppinions in a reasonable, mindful and respectful tone. But that's not the feeling one is left with when you shout at the world. At least for me the feeling I'm left with is of an old academic codger who observes the world from his ivory tower of lofty academic ideals and with a wrathful sneer shouts "Fool you, can't you ever do anything right!? I've told you all, over and over, how it's supposed to be done! And still you persist with your develish ignorance! Argh!! Now let me show you ignorants one more time!"
No one wants to hear something like that. It only breeds resentment. Is that what you want? Maybe not, but that's what you will get, if you don't change your tone.
The users of these forums are mostly people with a little real-life problem they need a quick fix for. They use Microsoft SQL Server, not a theoretical SQL engine with a perfect ANSI/ISO implementation. As long as it works and works well, fine by them, adhering to some standard or not. They want help solving a problem, not having the snot punched out of them for not following some artificial rules. They're not your academic peers, with whom you can happlily exchange insults.
Your entry this time isn't even the worst in the sneering department, it's actually quite laid back, compared to some of your "best". However, it's still quite disrespectful of the OP.
Of course, you may have some good points now and then, but it's almostly entirely overshadowed by the bad presentation and therefore most likely ignored.
Gently does it.
February 26, 2021 at 7:58 am
Johan Bijnens wrote:I do not consider "2359 to 2359" a valid case, as that might imply it may also span multiple days
Ordinarily, I'd agree but to consider the span of multiple days, the original data would need to include a date... and it does not. The limits of the data relegate this problem to no more than 24 hours... but not less.
If, however, 24 hours should not be considered, then just add the '=' symbol to the '<' symbol and Bob's your uncle.
I think it limits it to less than 24 hours. 2359 to 2359 (any same value to same value) should be a time of 0 minutes, not a time of 24 hours. It's minutes here, not seconds, so presumably if some error occurred the process might exit out in less than 1 minute.
If you allow overlaps of time, then is 0300 0301 1 minute or 24 hours and 1 minute?
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
February 26, 2021 at 2:19 pm
Three decades ago, I tried posting super consolatory postings. I tried fixing one and only one problem at a time when somebody posted a total mess. I found out that people simply ignore you when you're nice to them and think that you are approving of their crap.
At the other extreme, I used to get emails from Russians, and Indians who would tell me they had stolen a copy of one of my books and that I should do their homework for them because of that. I'm not kidding. They never even bothered to try anything, or even read the forum posting rules. DDL? What is that? Specs? Keys? Celko, you do that for me and do it now.
I tell people if you think I am rough on you, you never had a class under Ed Dykstra! He is the guy I used to tell his students bluntly that they were "doing everything completely wrong." When he graded papers or looked at their work.
Please post DDL and follow ANSI/ISO standards when asking for help.
February 26, 2021 at 4:05 pm
Three decades ago, I tried posting super consolatory postings. I tried fixing one and only one problem at a time when somebody posted a total mess. I found out that people simply ignore you when you're nice to them and think that you are approving of their crap.
At the other extreme, I used to get emails from Russians, and Indians who would tell me they had stolen a copy of one of my books and that I should do their homework for them because of that. I'm not kidding. They never even bothered to try anything, or even read the forum posting rules. DDL? What is that? Specs? Keys? Celko, you do that for me and do it now.
I tell people if you think I am rough on you, you never had a class under Ed Dykstra! He is the guy I used to tell his students bluntly that they were "doing everything completely wrong." When he graded papers or looked at their work.
Understood but the information in your posts is still ignored except that you also come across as an arrogant and haughty sot to those that might heed your advice. You also frequently provide the right way to "do data" but not a solution that uses that data. This thread is no exception... you chastised the Op for having bad data, provided an example of that you consider to be good data, and then never provided the Op with how to solve the problem with your good data.
Also, your info about Ed Dykstra has nothing to do with what we're talking about. You keep complaining about people living in the past... you might want to consider that bit of advice yourself. 😉
--Jeff Moden
February 26, 2021 at 5:01 pm
Jeff Moden wrote:Johan Bijnens wrote:I do not consider "2359 to 2359" a valid case, as that might imply it may also span multiple days
Ordinarily, I'd agree but to consider the span of multiple days, the original data would need to include a date... and it does not. The limits of the data relegate this problem to no more than 24 hours... but not less.
If, however, 24 hours should not be considered, then just add the '=' symbol to the '<' symbol and Bob's your uncle.
I think it limits it to less than 24 hours. 2359 to 2359 (any same value to same value) should be a time of 0 minutes, not a time of 24 hours. It's minutes here, not seconds, so presumably if some error occurred the process might exit out in less than 1 minute.
If you allow overlaps of time, then is 0300 0301 1 minute or 24 hours and 1 minute?
That could be. "It Depends" on what the OP needs. If they definitely want 2359/2359 to be 0 minutes, then just add an "=' sign to the "<" to make it "<=" and you're good to go.
Shifting gears a bit and to emphasize what I alluded to before, this is one of the primary reasons why I hate it when people separate dates and times to begin with. The OP should have an associated date column to solve the true issue of duration.
--Jeff Moden
February 27, 2021 at 1:48 pm
This was removed by the editor as SPAM
March 3, 2021 at 12:13 pm
Thanks for all your support
March 3, 2021 at 3:52 pm
So why did you just re-ask the exact same q?? Via q "Appttime add with duration how to get endtime please".
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply