I want to query of getting hour from date field

[hassanhundal007]

In my table the field of DateTime name is TIN. In this field DATE and TIME are shown for date I pass this query:

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:

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.

[code green]

Because HOUR() expects a TIME data type

[topher23]

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.

[Atli]

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:

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.

[ck9663]

Use the datepart() function.

Happy Coding!!!


--- CK

[nbiswas]

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

SELECT DATEPART(HH,GETDATE()) AS [Hour]
 
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