Time and date calculations - SQL Server Forums

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 2012 Forums

Transact-SQL (2012)

Time and date calculations

Author

Topic

JBRTaylor
Starting Member

5 Posts

Posted - 2013-06-16 : 03:34:18

I have written a query in ms Access that i have been using for some time now and works great but the time has come to rewrite it for sql server which is where i have come unstuck. I am currently stuck with the time calculations but the whole sql code from access is as follows:


SELECT tblCollection.HireID, tblCollectionItemized.HireLine, tblCollection.CollectionDate, tblCollection.DueBack, tblTariff.Tariff, ([DueBack]-[CollectionDate])/7 AS WeeksUsed, ([DueBack]-[CollectionDate]) AS DaysUsed, Int(([DueBack]-[CollectionDate])/7) AS Weeks, ((([DueBack]-[CollectionDate]))-Int(([DueBack]-[CollectionDate])/7)*7) AS Days, tblKitSubCat.CategoryID, tblCollection.DiscountPc, [WeekRate]/[DaysPerWeek] AS DayRate, (IIf([Days]<[DaysPerWeek],[DayRate]*[Days],[WeekRate]*1)+([Weeks]*[WeekRate])) AS DayTariffSub, [WeeksUsed]*[WeekRate] AS StdTariffSub, (IIf([DayRateApply]=True,(([DiscountPc]/-100)*[DayTariffSub])+[DayTariffSub],(([DiscountPc]/-100)*[StdTariffSub])+[StdTariffSub])) AS EstAmountDue, Round([EstAmountDue],2) AS EstAmountDueRnd
FROM ((tblTariff INNER JOIN (tblCollection INNER JOIN tblCollectionItemized ON tblCollection.HireID = tblCollectionItemized.HireID) ON tblTariff.TariffKey = tblCollection.Tarriff) INNER JOIN tblRateCard ON tblTariff.TariffKey = tblRateCard.Tariff) INNER JOIN tblKitSubCat ON (tblCollectionItemized.KitKey = tblKitSubCat.CategoryID) AND (tblRateCard.KitKey = tblKitSubCat.CategoryID)
WHERE (((tblCollection.HireID)=1532));

I have been using the graphical query builder in sql management studio seeing as i am a beginner and want to be able to have two columns which calculate the weeks and days between two dates. so if the difference is 10 days col 1 will read 1 week, and col two will read 3 days, or if the difference is 15 then, col 1 2 weeks and col 2 1day.

Thanks in advance
Jon

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-16 : 13:06:44

There may be a few things that you need to change, but regarding your specific question about calculating the number of days, instead of "Int(([DueBack]-[CollectionDate])/7)" use

DATEDIFF(day,[CollectionDate],[DueBack])/7

I did not use a cast there because the numerator and denominator are integers, so it will be an integer division (truncation of any fractional part).

There are some examples of DATEDIFF here: http://msdn.microsoft.com/en-us/library/ms189794.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-17 : 01:12:15

similarly DATEDIFF(day,[CollectionDate],[DueBack])%7 will give you the residual days after whole weeks.

see

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources