extract minute formula in 30 mins from datetime

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

General SQL Server Forums

New to SQL Server Programming

extract minute formula in 30 mins from datetime

Author

Topic

pavlos
Yak Posting Veteran

59 Posts

Posted - 2012-01-20 : 00:33:17

Hey all,

writing the following query
my finishdatetime variable is stored as a datetime and is in the form of dd/mm/yyyy xx:xx:xx.xx etc,

SELECT TOP 1000
*,
(Select DATEPART(HH, B.FINISHDATETIME)) as HOUR,
(Select DATEPART(MM, B.FINISHDATETIME)/30) as 30MINUTE
FROM
SALES AS B

The query runs but this doesnt work.
(Select DATEPART(MM, B.FINISHDATETIME)/30) as MINUTE
for every time that is xx:00 - xx:29 I want the minute column to return 0 and for every time xx:30 - xx:59 I want the minute column to return 1

eg (NOTE THIS IS AN EXAMPLE, I HAVE RETURNED * SO THERE WILL BE HEAPS OF COLUMNS)

DATE TIME | Hour | 30MIN
01/01/2012 4:01:02 PM | 16 | 0
01/01/2012 5:33:02 AM | 5 | 0

I can get the hour by just returning datepart hh

but i was thinking return datepart mm then dividing by 30 but it doesnt seem to work

cheers guys

pavlos
Yak Posting Veteran

59 Posts

Posted - 2012-01-20 : 00:35:38

ignore that guys
MM returns month..
i needed MI
and it works

LOL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources