Converting date to hour of the day

• astrid 69000

  Hall of Fame

  Points: 3208

  More actions

  January 23, 2014 at 8:26 am

  #281456

  hi,

  I have a question 😀

  I have a table with dates (datetime) and I need the dates to be converted to the hour of the day (from 0 to 23)

  This is my table

  create table #mydates (

  PRIMARY KEY (PDates),

  PDates datetime)

  INSERT INTO #mydates (PDates)

  select '2014-01-21 19:26:17.523' union all

  select '2014-01-21 21:21:00.693' union all

  select '2014-01-21 09:26:07.433' union all

  select '2014-01-21 21:26:16.850' union all

  select '2014-01-21 21:26:17.237' union all

  select '2014-01-21 15:19:00.920' union all

  select '2014-01-21 19:26:17.150' union all

  select '2014-01-21 01:19:00.317' union all

  select '2014-01-21 03:26:08.707' union all

  select '2014-01-21 16:24:01.073' union all

  select '2014-01-21 20:21:00.817' union all

  select '2014-01-21 22:21:00.800' union all

  select '2014-01-21 23:26:28.260'

  And my output needs to be something like

  What I want to get when it says ''2014-01-21 19:26:17.523" I want to get the hour 19, regardless of the day it is, same for "2014-01-21 21:21:00.693" I want to get the hour 21.

  Can someone help :w00t: pretty please :crying:

  Thanks

  Astrid

• sestell1

  SSChampion

  Points: 10230

  More actions

  January 23, 2014 at 8:34 am

  #1682827

  SELECT DATEPART(hh, PDates) FROM #mydates

• astrid 69000

  Hall of Fame

  Points: 3208

  More actions

  January 23, 2014 at 8:36 am

  #1682829

  thanks so much!!!! 😀

• sneumersky

  SSCertifiable

  Points: 7667

  More actions

  January 23, 2014 at 8:36 am

  #1682830

  You can use the datepart function. For example:

  select DATEPART(hh, '2014-01-21 19:26:17.523') will return 19.

  For hours, mins, and seconds you can use:

  select convert(time(0), '2014-01-21 19:26:17.523')

• astrid 69000

  Hall of Fame

  Points: 3208

  More actions

  January 23, 2014 at 8:38 am

  #1682831

  thanks to you too. google didnt help much this time around 😎