By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,179 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

I want to query of getting hour from date field

P: 1
In my table the field of DateTime name is TIN. In this field DATE and TIME are shown for date I pass this query:
Expand|Select|Wrap|Line Numbers
  1. SELECT DAY(TIN) FROM SHIFT WHERE SHID = 1
Then the result is "10".

So the same I want to get Hour fromt his field I pass this query:
Expand|Select|Wrap|Line Numbers
  1. SELECT HOUR(TIN) FROM SHIFT WHERE SHID = 1
But it gives this error:
'HOUR' is not a recognized function name.
So please help me and give me the complete query from getting hour.
Oct 24 '09 #1

✓ answered by nbiswas

Either use DATEPART() or DATENAME()
e.g.

Expand|Select|Wrap|Line Numbers
  1. SELECT DATEPART(HH,GETDATE()) AS [Hour]
  2.  
  3. SELECT DATENAME(HH, GETDATE())AS [Hour]
Refer this articles for more infos

a) DateName( ) http://msdn.microsoft.com/en-us/library/ms174395.aspx

b) DatePart() http://msdn.microsoft.com/en-us/library/ms174420.aspx

Hope this helps

Share this Question
Share on Google+
5 Replies


code green
Expert 100+
P: 1,726
Because HOUR() expects a TIME data type
Oct 26 '09 #2

topher23
Expert 100+
P: 234
If MySQL is anything like MS-SQL, it saves the date/time as a number along the lines of 1345.5467, where the whole number is the day and the fraction is the time of day. Performing a manipulation to pull the whole number off the fraction, then formatting the remaining decimal as a time value will accomplish the same result as the Hour() function.
Oct 26 '09 #3

Atli
Expert 5K+
P: 5,058
Based on the fact that HOUR is a valid MySQL function, and that the error message matches that of a MSSQL server (as far as I know), I am going to assume this is in fact a MSSQL question posted in the wrong forum.

If that is the case try this:
Expand|Select|Wrap|Line Numbers
  1. SELECT DATEPART('hh',TIN) WHERE SHID = 1
However, It has been years since I used MSSQL, so this could very well be wrong. (Worth a try tho.)

I'll move this over to the MSSQL forum now.
Oct 31 '09 #4

ck9663
Expert 2.5K+
P: 2,878
Use the datepart() function.

Happy Coding!!!


--- CK
Nov 1 '09 #5

nbiswas
100+
P: 149
Either use DATEPART() or DATENAME()
e.g.

Expand|Select|Wrap|Line Numbers
  1. SELECT DATEPART(HH,GETDATE()) AS [Hour]
  2.  
  3. SELECT DATENAME(HH, GETDATE())AS [Hour]
Refer this articles for more infos

a) DateName( ) http://msdn.microsoft.com/en-us/library/ms174395.aspx

b) DatePart() http://msdn.microsoft.com/en-us/library/ms174420.aspx

Hope this helps
Nov 8 '09 #6

Post your reply

Sign in to post your reply or Sign up for a free account.