Get check time from separate fields in Microsoft SQL Server
-
Hi, I am using aloha POS and they have the date for every check in separate fields and now I want to calculate the total time for the checks but unable to get the how of it.. 1. The date is DOB and it's datetime but I just need to extra the getdate() from it. 2. The open time is OPENHOUR and OPENMIN 3. The close time is CLOSEHOUR and CLOSEMIN so basically the open time for the check will be the DATE FROM DOB + OPENHOUR + OPENMIN and the close time will be DATE FROM DOB + CLOSEHOUR + CLOSEMIN How can I get the total minutes for the check? Thanks, Jassim[^]
Technology News @ www.JassimRahma.com
-
Hi, I am using aloha POS and they have the date for every check in separate fields and now I want to calculate the total time for the checks but unable to get the how of it.. 1. The date is DOB and it's datetime but I just need to extra the getdate() from it. 2. The open time is OPENHOUR and OPENMIN 3. The close time is CLOSEHOUR and CLOSEMIN so basically the open time for the check will be the DATE FROM DOB + OPENHOUR + OPENMIN and the close time will be DATE FROM DOB + CLOSEHOUR + CLOSEMIN How can I get the total minutes for the check? Thanks, Jassim[^]
Technology News @ www.JassimRahma.com
This might give you dome ideas, demonstrating the DATEADD and Convert Date possibilities
DECLARE @DOB DATETIME = GETDATE()
DECLARE @ODate DATE
DECLARE @OHour INT = 8
DECLARE @OMin INT = 23
DECLARE @OpenDT DATETIMESELECT @ODate = CONVERT(DATE,@DOB)
SET @OpenDT = @ODate
SELECT @OpenDT = DATEADD(HOUR,@OHour,@OpenDT)
SELECT @OpenDT = DATEADD(MINUTE,@OMin,@OpenDT)PRINT @OpenDT
Never underestimate the power of human stupidity RAH
-
This might give you dome ideas, demonstrating the DATEADD and Convert Date possibilities
DECLARE @DOB DATETIME = GETDATE()
DECLARE @ODate DATE
DECLARE @OHour INT = 8
DECLARE @OMin INT = 23
DECLARE @OpenDT DATETIMESELECT @ODate = CONVERT(DATE,@DOB)
SET @OpenDT = @ODate
SELECT @OpenDT = DATEADD(HOUR,@OHour,@OpenDT)
SELECT @OpenDT = DATEADD(MINUTE,@OMin,@OpenDT)PRINT @OpenDT
Never underestimate the power of human stupidity RAH
You can simplify slightly
DECLARE @ODate DATE
DECLARE @OHour INT = 8
DECLARE @OMin INT = 23
DECLARE @OpenDT DATETIME
declare @DOB DATE = getdate() --Or whatever date drom the database
SELECT @OpenDT = @DOBSELECT @OpenDT = DATEADD(MINUTE,(@OHour * 60) + @OMin, @OpenDT)
PRINT @OpenDT
========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================
-
You can simplify slightly
DECLARE @ODate DATE
DECLARE @OHour INT = 8
DECLARE @OMin INT = 23
DECLARE @OpenDT DATETIME
declare @DOB DATE = getdate() --Or whatever date drom the database
SELECT @OpenDT = @DOBSELECT @OpenDT = DATEADD(MINUTE,(@OHour * 60) + @OMin, @OpenDT)
PRINT @OpenDT
========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================
You've declared
@ODateasDATE, so you can't add time parts to it.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
You've declared
@ODateasDATE, so you can't add time parts to it.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
I'm adding the minutes to @OpenDT so it does work - @ODate is superfluous and can be removed
========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================
