Extracting the hh:mm from datetime field

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

Transact-SQL (2005)

Extracting the hh:mm from datetime field

Author

Topic

~Carl
Starting Member

3 Posts

Posted - 2007-03-08 : 09:52:05

I'm trying to write a query where I check a datetime field in order to see if an event took place during business hours.

So, from the datetime value '3/2/2007 1:31:22 PM', I'd like to extract '1:31 PM' or '13:31'.

The only way I have found is to cast values obtained with the DATEPART function:

CAST(
 CAST(DATEPART(hh, StartTime) AS VarChar(2)) +
 ':' +
 CAST(DATEPART(mi, StartTime) AS VarChar(2))
AS VarChar(5)
)

It seems there has to be a more elegant way of getting this information, but I haven't been able to find anything. Any ideas?

spejbl
Starting Member

28 Posts

Posted - 2007-03-08 : 10:34:16

SELECT CONVERT(VARCHAR(5), StartDate, 114)

kb
http://kbupdate.info/

~Carl
Starting Member

3 Posts

Posted - 2007-03-08 : 10:54:58

Ha... I knew I was missing something! For some reason I didn't think I could truncate the seconds and milliseconds by shortening the length of the VARCHAR. Thanks.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-03-08 : 13:06:34

Dunno if this helps, but you could also do date calculations instead of converting to varchar which may or maynot be faster.

For example:

-- Note: Replace @GETDATE() with StartTime

SELECT DATEDIFF(MINUTE, DATEDIFF(DAY, 0, GETDATE()), GETDATE())
SELECT DATEDIFF(HOUR, DATEDIFF(DAY, 0, GETDATE()), GETDATE())

-- Example (between 9am and 5pm):
WHERE
    DATEDIFF(HOUR, DATEDIFF(DAY, 0, StartDate), StartDate) >= 9
    AND DATEDIFF(HOUR, DATEDIFF(DAY, 0, StartDate), StartDate) < 17

This is assuming you are looking for rows with a startdate during business hours on more than one day (i.e. not today). Otherwise you would want to do the date calculation on GETDATE() or some other date and compare that to StartDate as to avoid doing calculations on the field itself.

-Ryan

~Carl
Starting Member

3 Posts

Posted - 2007-03-08 : 13:23:56

Thanks, Ryan. Another good method. I think the method I use will come down to how the business hours are input/stored since speed isn't a factor.

Thanks again, guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources