Subtract Two DateTime Columns And Get Minutes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Subtract Two DateTime Columns And Get Minutes

Author

Topic

uneek78
Starting Member

13 Posts

Posted - 2012-12-18 : 13:31:48

I have the following:

SELECT ExplorerSnapshot.SignerLastName, ExplorerSnapshot.SignerFirstName,
ExplorerSnapshot.DictatorLastName, ExplorerSnapshot.DictatorFirstName,
"dbo"."Order".CreateDate, "dbo"."Order".CompleteDate,
Report.BeginEditDate, Report.LastPrelimDate,
Report.LastSignDate, Report.IsAddendum

FROM "dbo"."Order", ExplorerSnapshot, Report

WHERE Report.ReportID = "dbo"."Order".ReportID
AND ExplorerSnapshot.ReportID = "dbo"."Order".ReportID
AND ExplorerSnapshot.OrderDate BETWEEN '12/15/2012' and '12/18/2012'
;

My goal is to have:

Report.LastSignDate subtract from Report.LastPrelimDate and give minutes

Report.LastPrelimDate subtract from Report.BeginEditDate and give minutes

Report.BeginEditDate subtract from "dbo"."Order".CompleteDate and give minutes

"dbo"."Order".CompleteDate subtract from "dbo"."Order".CreateDate and give minutes

Is that possible?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-18 : 13:49:07

You can add columns such as shown below:

....
Report.BeginEditDate, Report.LastPrelimDate,
Report.LastSignDate, Report.IsAddendum,
DATEDIFF(minute, Report.LastPrelimDate, Report.LastSignDate),
DATEDIFF(minute, Report.LastPrelimDate, Report.BeginEditDate)

FROM "dbo"."Order", ExplorerSnapshot, Report
...

uneek78
Starting Member

13 Posts

Posted - 2012-12-18 : 16:28:17

THANK YOU!!!!! You're a genius and it worked!!!! Of all the hours of googling and I got nothing that worked. Yours worked! THANKS!!!!

uneek78
Starting Member

13 Posts

Posted - 2012-12-18 : 16:29:57

Soooooooooooooooooo(lol) how could I give each of those columns a specific name?

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-12-18 : 19:51:57

Give each one an alias:[CODE]DATEDIFF(minute, Report.LastPrelimDate, Report.LastSignDate) as MinutesToSign,
DATEDIFF(minute, Report.LastPrelimDate, Report.BeginEditDate) as MinutesTostart[/CODE]

=================================================
Hear the sledges with the bells - silver bells!
What a world of merriment their melody foretells!

uneek78
Starting Member

13 Posts

Posted - 2012-12-19 : 09:48:25

Aaaaaah..........much appreciated my friend.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-19 : 09:55:44

quote:
Originally posted by uneek78

Aaaaaah..........much appreciated my friend.

see this to understand how date operations are done in t-sql

http://visakhm.blogspot.in/2012/07/generate-datetime-values-from-integers.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources