Check if time in DateTime is greater then 0500? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Check if time in DateTime is greater then 0500?

Author

Topic

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2013-08-08 : 16:02:59

I have a DATETIME variable (@EndDate) that I need to make sure the time portion of is after 5AM (so 501 AM is valid but not 5AM). I know with datepart you can grab the hour OR the time. I was thinking convert to varchar but was not sure if there was an easier way. Anyone have any suggestions or is converting the variable to varchar the quickest way? Thanks

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-08 : 16:21:41

You can use datepart to grab both hours and minutes like this:

SELECT DATEPART(HOUR, @EndDate) as [Hours];
SELECT DATEPART(MINUTE, @EndDate) as [Minutes];

Compare Hours >= 5 and Minutes >0

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2013-08-09 : 09:17:52

That would not work, the check would fail for 6AM

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

Ifor
Aged Yak Warrior

700 Posts

Posted - 2013-08-09 : 09:54:45

-- *** Test Data ***
CREATE TABLE #t
(
EndDate datetime NOT NULL
);
INSERT INTO #t
SELECT '20130809 04:59:00'
UNION ALL SELECT '20130809 04:59:30'
UNION ALL SELECT '20130809 05:00:00'
UNION ALL SELECT '20130809 05:00:30'
UNION ALL SELECT '20130809 05:01:00';
-- *** End Test Data ***

SELECT EndDate
,CASE
WHEN DATEDIFF(ms, DATEADD(day, DATEDIFF(day, 0, EndDate), 0), EndDate) > 18000000
THEN 'Valid'
ELSE 'Invalid'
END AS TimeStatus
FROM #t

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-09 : 10:01:10

Since you are on SQL 2012 (or any version starting with SQL 2008), you can use TIME data type to do this as well. Borrowing Ifor's sample data

SELECT  
	EndDate ,
	CASE 
		WHEN CAST(EndDate AS TIME) > '05:00:00.0000000' THEN 'Valid'
		ELSE 'Invalid'
	END AS TimeStatus
FROM    #t

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources