MS SQL Server - Time

Author

Time - Hours

JenC

2006-02-16, 7:23 am

Hi,

I am trying to retrieve hours from a datetime field in a SQL table. I have
converted the datetime field to a varchar which is fine but the hours come
out as, for example, 1AM or 1PM. What I wanted was either 1 or 13.

i.e. 2006-02-07 16:23:00
I want the hours to be 16 not 4PM.

Is there a way to do this?

Thanks in advance.

Regards,
J

Tibor Karaszi

2006-02-16, 7:23 am

SELECT CONVERT(varchar(2), CAST('20060212 14:12:23' AS datetime), 108)

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/

"JenC" <JenC@discussions.microsoft.com> wrote in message
news:35BF05D5-F329-44C0-A653- 6912D9211D28@microso
ft.com...
> Hi,
>
> I am trying to retrieve hours from a datetime field in a SQL table. I have
> converted the datetime field to a varchar which is fine but the hours come
> out as, for example, 1AM or 1PM. What I wanted was either 1 or 13.
>
> i.e. 2006-02-07 16:23:00
> I want the hours to be 16 not 4PM.
>
> Is there a way to do this?
>
> Thanks in advance.
>
> Regards,
> J

Uri Dimant

2006-02-16, 7:23 am

JenC
SELECT REVERSE(LEFT(STUFF(R
EVERSE(
CONVERT(varchar,getd
ate(),9)),3,4,SPACE(
0)),11))

-----------------
select {fn extract(HOUR from getdate())}

"JenC" <JenC@discussions.microsoft.com> wrote in message
news:35BF05D5-F329-44C0-A653- 6912D9211D28@microso
ft.com...
> Hi,
>
> I am trying to retrieve hours from a datetime field in a SQL table. I have
> converted the datetime field to a varchar which is fine but the hours come
> out as, for example, 1AM or 1PM. What I wanted was either 1 or 13.
>
> i.e. 2006-02-07 16:23:00
> I want the hours to be 16 not 4PM.
>
> Is there a way to do this?
>
> Thanks in advance.
>
> Regards,
> J

Steen Persson (DK)

2006-02-16, 7:23 am

JenC wrote:
> Hi,
>
> I am trying to retrieve hours from a datetime field in a SQL table. I have
> converted the datetime field to a varchar which is fine but the hours come
> out as, for example, 1AM or 1PM. What I wanted was either 1 or 13.
>
> i.e. 2006-02-07 16:23:00
> I want the hours to be 16 not 4PM.
>
> Is there a way to do this?
>
> Thanks in advance.
>
> Regards,
> J

If you are converting the datetime value to VARCHAR, you can use one of
the types for 24H time format - e.g. 13/113, 114, 20/120. You can look
them up in Books On Line under CAST/CONVERT command.

Regards
Steen

JenC

2006-02-16, 7:23 am

Thanks for the reply's and help, all sorted now.

Cheers,
J

Dutt

2006-02-17, 7:23 am

Hi,
It could be easily done by using

u'll get hours directly from this statement but in varchar
datatype-------------------
select (convert(varchar(20)
,getdate()))
If u wanna have the result in integer datatype ,we can convert
it.....................
select convert(int,substrin
g(convert(varchar(20
),getdate()),13,2))
Have a nice time....
bye

Steen Persson (DK)

2006-02-17, 7:23 am

Dutt wrote:
> Hi,
> It could be easily done by using
>
> u'll get hours directly from this statement but in varchar
> datatype-------------------
> select (convert(varchar(20)
,getdate()))
>....

That will actually not give what the poster asked for - that will give
you the time with AM/PM.
Running SELECT(CONVERT(varch
ar(20), GETDATE(), 113) (or SELECT
(CONVERT(varchar(20)
, GETDATE(), 108) if he only want the time part)
will do the trick.

Regards
Steen

Sponsored Links