April 3, 2017 at 3:46 pm
Hello All,
I have two columns begindate and enddate ,i need help in finding exact difference in days and hours .
By using datediff i am getting difference in integer but for first row in Hr_diff(Hours difference) column i want data to get actual hours difference i.e, 2.56 (2 hours 56 minutes)
and for the second row in Dy_diff (days difference) column i want data to get actual days difference i.e, i want data to get 2.23. (2 days 23 hours 49 minuts(approx)
Thanks in advance for your help.
declare @a table
(
begindate varchar(50),
enddate varchar(50)
)
insert into @a values ('2017-02-07 09:56:00.000','2017-02-07 12:51:00.000')
insert into @a values ('2017-03-17 16:33:00.000','2017-03-20 15:42:00.000')
Select begindate
,enddate
,DATEDIFF(Hour,begindate,enddate) AS Hr_diff
,DATEDIFF(Day,begindate,enddate) AS Dy_diff from @a
April 3, 2017 at 4:22 pm
Get the difference in MINUTEs and then compute all the needed values from that value.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 4, 2017 at 3:58 am
This might also help: http://www.sqlservercentral.com/articles/Age+Calculation/153796/
April 4, 2017 at 2:26 pm
ScottPletcher - Monday, April 3, 2017 4:22 PMGet the difference in MINUTEs and then compute all the needed values from that value.
Thank you, it worked for me . Also find the below post regarding same issue.
https://www.sqlservercentral.com/Forums/490411/Converting-minutes-to-days-and-hours
April 4, 2017 at 4:47 pm
dhanekulakalyan - Monday, April 3, 2017 3:46 PMHello All,
I have two columns begindate and enddate ,i need help in finding exact difference in days and hours .
By using datediff i am getting difference in integer but for first row in Hr_diff(Hours difference) column i want data to get actual hours difference i.e, 2.56 (2 hours 56 minutes)
and for the second row in Dy_diff (days difference) column i want data to get actual days difference i.e, i want data to get 2.23. (2 days 23 hours 49 minuts(approx)
Thanks in advance for your help.
declare @a table
(
begindate varchar(50),
enddate varchar(50)
)
insert into @a values ('2017-02-07 09:56:00.000','2017-02-07 12:51:00.000')
insert into @a values ('2017-03-17 16:33:00.000','2017-03-20 15:42:00.000')
Select begindate
,enddate
,DATEDIFF(Hour,begindate,enddate) AS Hr_diff
,DATEDIFF(Day,begindate,enddate) AS Dy_diff from @a
What is the actual datatype of BeginDate and EndDate in your real table?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 5, 2017 at 9:33 am
This article by Itzik Ben-Gan should put you on the right path...
April 5, 2017 at 9:53 am
This article solves a similar problem: http://www.sqlservercentral.com/articles/T-SQL/103343/
However, if you need values in separate columns, here's an option.
declare @a table(
begindate varchar(50),
enddate varchar(50)
);
insert into @a
values ('2017-02-07 09:56:00.000','2017-02-07 12:51:00.000'),
('2017-03-17 16:33:00.000','2017-03-20 15:42:00.000');
Select begindate
,enddate
,DATEDIFF(HOUR,begindate,enddate)/24 AS Dy_diff
,DATEDIFF(MINUTE,begindate,enddate)/60%24 AS Hr_diff
,DATEDIFF(MINUTE,begindate,enddate)%60 AS Min_diff
from @a;
April 7, 2017 at 9:35 am
Jeff Moden - Tuesday, April 4, 2017 4:47 PMdhanekulakalyan - Monday, April 3, 2017 3:46 PMHello All,
I have two columns begindate and enddate ,i need help in finding exact difference in days and hours .
By using datediff i am getting difference in integer but for first row in Hr_diff(Hours difference) column i want data to get actual hours difference i.e, 2.56 (2 hours 56 minutes)
and for the second row in Dy_diff (days difference) column i want data to get actual days difference i.e, i want data to get 2.23. (2 days 23 hours 49 minuts(approx)
Thanks in advance for your help.
declare @a table
(
begindate varchar(50),
enddate varchar(50)
)
insert into @a values ('2017-02-07 09:56:00.000','2017-02-07 12:51:00.000')
insert into @a values ('2017-03-17 16:33:00.000','2017-03-20 15:42:00.000')
Select begindate
,enddate
,DATEDIFF(Hour,begindate,enddate) AS Hr_diff
,DATEDIFF(Day,begindate,enddate) AS Dy_diff from @a
What is the actual datatype of BeginDate and EndDate in your real table?
VARCHAR
April 7, 2017 at 10:17 pm
dhanekulakalyan - Friday, April 7, 2017 9:35 AMJeff Moden - Tuesday, April 4, 2017 4:47 PMWhat is the actual datatype of BeginDate and EndDate in your real table?
VARCHAR
This error has to be fixed.
_____________
Code for TallyGenerator
April 8, 2017 at 9:49 am
Sergiy - Friday, April 7, 2017 10:17 PMdhanekulakalyan - Friday, April 7, 2017 9:35 AMJeff Moden - Tuesday, April 4, 2017 4:47 PMWhat is the actual datatype of BeginDate and EndDate in your real table?
VARCHAR
This error has to be fixed.
Sergiy is absolutely correct here. Using VARCHAR for temporal columns is a really bad idea (one of the worst, actually). It allows for non-temporal data and a wealth of other errors to be introduced into the columns. It also makes it much more difficult to do high performance temporal math such as calculating a simple duration in days, hours, and minutes between two dates and times.
That's just the beginning. Here's more...
1. Every temporal calculation will require either implicit or explicit conversions requiring extra CPU and Duration.
2. A VARCHAR version of '2017-02-07 09:56:00.000' requires 25 bytes of storage (1 byte for each character and 2 bytes for the length characteristic of the varchar) whereas a DATETIME column only requires 8 bytes.
3. If an end date is unknown at the time of insert, a NULL will be used, which takes virtually no space in the row itself. When the row is updated with and end date, the row will need to expand to accommodate the added data. If there's no room on the page, an expensive page split will occur. If there is room on the page, the row will still need to be moved within the page. And that also happens for every index that may be on the column.
4. The extra space used by VARCHAR dates and times doesn't occur just on disk. It also occurs in memory when the table is loaded. The simple example in 2 above will use 3 times as much memory as a real DATETIME datatype.
5. Then, there's the cost in backups. If you're doing the backup to disk and then back that up to tape, you're using 3 times the disk space, slowing backups down, slowing restores down, and using 3 times as much space on the tape.
Like Sergiy said, "This error has to be fixed"!
--Jeff Moden
Change is inevitable... Change for the better is not.
April 9, 2017 at 7:12 am
Jeff Moden - Saturday, April 8, 2017 9:49 AMSergiy - Friday, April 7, 2017 10:17 PMdhanekulakalyan - Friday, April 7, 2017 9:35 AMJeff Moden - Tuesday, April 4, 2017 4:47 PMWhat is the actual datatype of BeginDate and EndDate in your real table?
VARCHAR
This error has to be fixed.
Sergiy is absolutely correct here. Using VARCHAR for temporal columns is a really bad idea (one of the worst, actually). It allows for non-temporal data and a wealth of other errors to be introduced into the columns. It also makes it much more difficult to do high performance temporal math such as calculating a simple duration in days, hours, and minutes between two dates and times.
That's just the beginning. Here's more...
1. Every temporal calculation will require either implicit or explicit conversions requiring extra CPU and Duration.
2. A VARCHAR version of '2017-02-07 09:56:00.000' requires 25 bytes of storage (1 byte for each character and 2 bytes for the length characteristic of the varchar) whereas a DATETIME column only requires 8 bytes.
3. If an end date is unknown at the time of insert, a NULL will be used, which takes virtually no space in the row itself. When the row is updated with and end date, the row will need to expand to accommodate the added data. If there's no room on the page, an expensive page split will occur. If there is room on the page, the row will still need to be moved within the page. And that also happens for every index that may be on the column.
4. The extra space used by VARCHAR dates and times doesn't occur just on disk. It also occurs in memory when the table is loaded. The simple example in 2 above will use 3 times as much memory as a real DATETIME datatype.
5. Then, there's the cost in backups. If you're doing the backup to disk and then back that up to tape, you're using 3 times the disk space, slowing backups down, slowing restores down, and using 3 times as much space on the tape.Like Sergiy said, "This error has to be fixed"!
One more thing to consider is that the varchar allows invalid data in the column, whereas a datetime does not. For example, a datetime value of '2017-02-31 09:56:00.000' isn't valid, but a varchar allows it. Then you'll have a really rough time with your calculations.
Add another vote for Sergiy's statement - this error has to be fixed.
April 10, 2017 at 8:04 am
Ed Wagner - Sunday, April 9, 2017 7:12 AMOne more thing to consider is that the varchar allows invalid data in the column, whereas a datetime does not. For example, a datetime value of '2017-02-31 09:56:00.000' isn't valid, but a varchar allows it. Then you'll have a really rough time with your calculations.Add another vote for Sergiy's statement - this error has to be fixed.
That was in the second sentence of the first paragraph of my response but I'm glad that someone emphasized it because of the world of pain that it will cause when (not IF, WHEN!) it happens.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 10, 2017 at 9:04 am
Jeff Moden - Monday, April 10, 2017 8:04 AMEd Wagner - Sunday, April 9, 2017 7:12 AMOne more thing to consider is that the varchar allows invalid data in the column, whereas a datetime does not. For example, a datetime value of '2017-02-31 09:56:00.000' isn't valid, but a varchar allows it. Then you'll have a really rough time with your calculations.Add another vote for Sergiy's statement - this error has to be fixed.
That was in the second sentence of the first paragraph of my response but I'm glad that someone emphasized it because of the world of pain that it will cause when (not IF, WHEN!) it happens.
Touche, sir. Exactly right.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply