UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Subtract Time From Midnight, Any Versions    
 
   
ordnance1
post May 18 2019, 07:48 PM
Post#1



Posts: 634
Joined: 7-May 11



I am using the line below to caculate the total hours and minutes of daylight, in this case the returned value was 15:25. how can I now display the total hours and minutes of darkness?

CODE
CONVERT (varchar(5), DATEADD(minute, DATEDIFF(minute, Sunrise, Sunset), '12:00:00 AM'), 114)
Go to the top of the page
 
GroverParkGeorge
post May 18 2019, 08:15 PM
Post#2


UA Admin
Posts: 35,325
Joined: 20-June 02
From: Newcastle, WA


There are actually two different things going on with dates and times.

First, there are calendar dates and clock times.

Second, there are elapsed times.

You want the elapsed time, but you are calculating it as a clock time by using DateAdd.

Each elapsed hour is 60 minutes long. So what you need to do is get the number of minutes and divide that by 60 to convert to elapsed hours. Any remainder will be the minutes elapsed.

Do you need more specific math formulas to do that? Or can you take it from there?

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
kfield7
post May 18 2019, 08:51 PM
Post#3



Posts: 971
Joined: 12-November 03
From: Iowa Lot


If Sunrise and Sunset are full date/time values (e.g., June 2, 2019 05:30:00 a.m.) then you only need to subtract the values to get day fractions:

e.g., LightHours = (sunset - sunrise) * 24
DarkHours = 24 - LightHours

Then you don't need to worry about midnight.

Now if you want continuous darkness through the night, you'd need

NightTime = (SunriseTomorrow - SunsetTonight) * 24

Unless it's a clear sky with full moon smile.gif
Go to the top of the page
 
ordnance1
post May 19 2019, 07:34 AM
Post#4



Posts: 634
Joined: 7-May 11



Thanks for the help. Sunrise and Sunset were time values only. Here was my final solution:

CODE
CONVERT (varchar(5), DATEADD(minute, DATEDIFF(minute, Sunrise, Sunset), '12:00:00 AM'), 114) AS Daylight
CONVERT (varchar(5), DATEADD(minute, 1440 - DATEDIFF(minute, Sunrise, Sunset), '12:00:00 AM'), 114) AS Darkness

Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    22nd July 2019 - 01:37 PM