How to display the time period from a Datetime

• cesidoo

  Newbie

  Points: 1

  More actions

  August 7, 2009 at 2:46 am

  #97967

  Hello everyone,

  I'm disapointed around problems of fct datetime:-)

  I have a datetime column and I want to display the time period of the days in a datagrid:

  For exemple :

  H1 = Monday to Friday, from 06:00:00 to 21:59:59

  H2 = Monday to Friday, from 22:00:00 to 05:59:59

  H3 = Saturday from 6:00:00 to 14:00:00

  H4 = The rest of time

  In fact, "07/08/2009 06:00:01" must return to my dategrid "H1"

  It is now 3 days that I have tested all the possibilities to work around this and never get there.

  Finally, is it possible please?

  A big thank you for your help and sorry for my bad Englsh.

  Thierry

• Ian Scarlett

  SSC-Insane

  Points: 23197

  More actions

  August 7, 2009 at 3:55 am

  #1035859

  This should give you the rough idea:-

  declare @date datetime

  declare @dw int

  declare @time datetime

  set @date = '7 aug 2009 21:59:59' --whatever date you want

  SET @time = @date - dateadd(dd, datediff(dd,0, @date),0)

  SET @dw = datepart(dw,@date)

  SELECT

  CASE WHEN @dw between 2 and 6 and @time >= '06:00:00' AND @time = '22:00:00' THEN 'H2'

  WHEN @dw between 2 and 6 and @time >= '00:00:00' AND @time < '06:00:00' THEN 'H2'

  WHEN @dw = 7 AND @time BETWEEN '06:00:00' AND '14:00:00' THEN 'H3'

  ELSE 'H4'

  END AS Period

  Should H3 be between 06:00:00 and 13:59:59 (not 14:00:00). I'll leave you to change the code if so.