Link to home
Create AccountLog in
Avatar of aneilg
aneilgFlag for United Kingdom of Great Britain and Northern Ireland

asked on

SQL DatePart

I've got the following query but cannot figure out why i am getting the results i am.
SELECT
            CTS.[TimeValue],
            MIN(DATEPART(hour,[CTS].[TimeValue])*60)+(DATEPART(nanosecond,[CTS].[TimeValue]))

2010-01-27 14:42:00.000      840
2012-03-05 13:37:00.000      780
2011-05-27 10:52:00.000      600
2010-04-23 14:30:00.000      840
2012-05-25 15:00:00.000      900
2010-06-04 14:47:00.000      840

as you can see i have 840 minutes for three results but different times.

thanks.
Avatar of Cluskitt
Cluskitt
Flag of Portugal image

You're trying to add nanoseconds to the hour. The result you're getting is the hour part only. You probably want to change nanosecond to minute.
Avatar of Andrew Crofts
Nanosecond

I get an error saying it is not a valid datepart. I am on SQL 2008

840 is the number for the hours (14) the second part is returning zero, presumably because the dates only go as far as milliseconds and nanoseconds are always zero
Avatar of aneilg

ASKER

ok cool, thanks for that.how does it actually calculate the minutes.
wheres the starting point.
ASKER CERTIFIED SOLUTION
Avatar of Cluskitt
Cluskitt
Flag of Portugal image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of aneilg

ASKER

thanks guy, the help you guys give is fantastic. i think i need to ask requirements what starting point they want. i think its 09:00am.

thanks.
If you want to use another time for starting point, you can simply use the datediff solution I gave you and replace '00:00' with whatever hour you want. Keep in mind, though, that if your date is set before 9h, it will return a negative number.
Not sure what it is you want to accomplish. Given your example time, what do expect to see as results?
Avatar of aneilg

ASKER

thanks