CONVERT AND TIMES AFTER MIDNIGHT

• mallybee

  Mr or Mrs. 500

  Points: 584

  More actions

  April 27, 2012 at 3:29 am

  #260620

  To simplify. I want the statement to equal 1 when the following criteria is met:

  Walktime = 2012-04-01 20:00:00:000 MINUS Validated AppointmentContactEnded = 2012-04-02 02:00:00:000

  is greater than 4 hours. In this case it would be 6 hours so would have the value of 1.

  Hi

  I have the following SQL statement:

  case when (convert(varchar(5),[Validated AppointmentContactEnded]-WalkinTime,108) > '04:00')

  then 1 end as 'TEST',

  It works ok with the exception of when the [Validated AppointmentContactEnded] time is after midnight on the next day e.g.

  ValAppConEnd = 01:30 minus WalkinTime = 20:00

  This should give a value of 1 but it doesn't.

  The time values before they are converted are stored as

  yyyy-mm-dd hh:mm:ss:sss

  Is there a way around this?

• MarkusB

  SSC-Dedicated

  Points: 37370

  More actions

  April 27, 2012 at 6:01 am

  #1480327

  Not sure if I understand your requirement correctly but I think this should work:

  SELECT

  CASE WHEN DATEDIFF(hh,[WalkinTime],[Validated AppointmentContactEnded]) < 4

  THEN 1

  ELSE 0

  END

  [font="Verdana"]Markus Bohse[/font]

• mallybee

  Mr or Mrs. 500

  Points: 584

  More actions

  April 27, 2012 at 7:55 am

  #1480436

  Thanks Markus

  Works perfectly.

  Kind regards

  Malcolm