Hour not showing correctly

• philip.davy

  SSC Veteran

  Points: 207

  More actions

  May 17, 2013 at 5:02 am

  #272816

  --Question 4b

  select

  History_Type_,

  TimeClicked,

  left(right(TimeClicked,11),2) as HourTimeClicked,

  count(left(right(TimeClicked,11),2)) as CountOfHourTimeClicked

  from clicktracking_ --with (nolock)

  inner join members_ on clicktracking_.MemberID_ = Members_.MemberID_

  group by

  History_Type_,

  timeClicked,

  left(right(TimeClicked,11),2)

  OPN2011-09-24 18:38:000169

  OPN1802013-04-02 12:41:00011

  OPN902013-04-11 18:18:00011

  OPN1802011-09-26 13:08:000113

  OPN902011-07-05 11:01:000117

  the hour is always incorrectly 01 which is wrong

  please can someone help?

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  May 17, 2013 at 5:41 am

  #1616128

  philip.davy (5/17/2013)

  ---

  --Question 4b

  select

  History_Type_,

  TimeClicked,

  left(right(TimeClicked,11),2) as HourTimeClicked,

  count(left(right(TimeClicked,11),2)) as CountOfHourTimeClicked

  from clicktracking_ --with (nolock)

  inner join members_ on clicktracking_.MemberID_ = Members_.MemberID_

  group by

  History_Type_,

  timeClicked,

  left(right(TimeClicked,11),2)

  OPN2011-09-24 18:38:000169

  OPN1802013-04-02 12:41:00011

  OPN902013-04-11 18:18:00011

  OPN1802011-09-26 13:08:000113

  OPN902011-07-05 11:01:000117

  the hour is always incorrectly 01 which is wrong

  please can someone help?

  Don't try to parse datetime as string. Use relevant datetime functions instead:

  SELECT ...

  DATEPART(HOUR,TimeClicked) AS HourTimeClicked

  ....

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]

• philip.davy

  SSC Veteran

  Points: 207

  More actions

  May 20, 2013 at 2:16 am

  #1616530

  Many thanks - this works perfectly