How to display the time period from a Datetime

[cesidoo]

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

[sknake]

Its a little messy but here you go:

--Get test data
IF OBJECT_ID('tempdb..#Test', 'U') IS NOT NULL DROP TABLE #Test
Create Table #Test
(
  OrderDate DateTime,
  ExpectedValue varchar(2)
)
Insert Into #Test (OrderDate, ExpectedValue) Values (Cast('2009-08-07 09:48:12.793' as DateTime), 'H1')
Insert Into #Test (OrderDate, ExpectedValue) Values (Cast('2009-08-07 04:48:12.793' as DateTime), 'H2')
Insert Into #Test (OrderDate, ExpectedValue) Values (Cast('2009-08-08 08:48:12.793' as DateTime), 'H3')
Insert Into #Test (OrderDate, ExpectedValue) Values (Cast('2009-08-09 08:48:12.793' as DateTime), 'H4')

--Create a function
GO
IF OBJECT_ID('GetTimePortion', 'FN') IS NOT NULL DROP FUNCTION GetTimePortion
GO
CREATE FUNCTION GetTimePortion(@dt DateTime)
RETURNS FLOAT
As
BEGIN
  RETURN Cast(@dt as float) - Floor(Cast(@dt as float))
END
GO

--Start the work. This is a little messy. You should probably create a function.
Declare @BaseDate Datetime Set @BaseDate = Cast(0 as DateTime)

Declare 
@H1Begin DateTime,
@H1End DateTime,
@H3Begin DateTime,
@H3End DateTime

Set @H1Begin = DateAdd(hour, 6, @BaseDate)
Set @H1End = DateAdd(second, 59, DateAdd(minute, 59, DateAdd(hour, 21, @BaseDate)))

Set @H3Begin = DateAdd(hour, 6, @BaseDate)
Set @H3End = DateAdd(hour, 14, @BaseDate)




Select *, 
( 
  Case 
  When (DatePart(dw, OrderDate) >= 2 and DatePart(dw, OrderDate) <= 6 and dbo.GetTimePortion(OrderDate) >= @H1Begin and dbo.GetTimePortion(OrderDate) <= @H1End) Then 'H1'
  When (DatePart(dw, OrderDate) >= 2 and DatePart(dw, OrderDate) <= 6) Then 'H2'
  When (DatePart(dw, OrderDate) = 7 and dbo.GetTimePortion(OrderDate) >= @H3Begin and dbo.GetTimePortion(OrderDate) <= @H3End) Then 'H3'
  Else 'H4'
  End
)
From #Test

[cesidoo]

Wahoo, remarkable.
I test this now!
Thanks:)

Its a little messy but here you go:

--Get test data
IF OBJECT_ID('tempdb..#Test', 'U') IS NOT NULL DROP TABLE #Test
Create Table #Test
(
  OrderDate DateTime,
  ExpectedValue varchar(2)
)
Insert Into #Test (OrderDate, ExpectedValue) Values (Cast('2009-08-07 09:48:12.793' as DateTime), 'H1')
Insert Into #Test (OrderDate, ExpectedValue) Values (Cast('2009-08-07 04:48:12.793' as DateTime), 'H2')
Insert Into #Test (OrderDate, ExpectedValue) Values (Cast('2009-08-08 08:48:12.793' as DateTime), 'H3')
Insert Into #Test (OrderDate, ExpectedValue) Values (Cast('2009-08-09 08:48:12.793' as DateTime), 'H4')

--Create a function
GO
IF OBJECT_ID('GetTimePortion', 'FN') IS NOT NULL DROP FUNCTION GetTimePortion
GO
CREATE FUNCTION GetTimePortion(@dt DateTime)
RETURNS FLOAT
As
BEGIN
  RETURN Cast(@dt as float) - Floor(Cast(@dt as float))
END
GO

--Start the work. This is a little messy. You should probably create a function.
Declare @BaseDate Datetime Set @BaseDate = Cast(0 as DateTime)

Declare 
@H1Begin DateTime,
@H1End DateTime,
@H3Begin DateTime,
@H3End DateTime

Set @H1Begin = DateAdd(hour, 6, @BaseDate)
Set @H1End = DateAdd(second, 59, DateAdd(minute, 59, DateAdd(hour, 21, @BaseDate)))

Set @H3Begin = DateAdd(hour, 6, @BaseDate)
Set @H3End = DateAdd(hour, 14, @BaseDate)




Select *, 
( 
  Case 
  When (DatePart(dw, OrderDate) >= 2 and DatePart(dw, OrderDate) <= 6 and dbo.GetTimePortion(OrderDate) >= @H1Begin and dbo.GetTimePortion(OrderDate) <= @H1End) Then 'H1'
  When (DatePart(dw, OrderDate) >= 2 and DatePart(dw, OrderDate) <= 6) Then 'H2'
  When (DatePart(dw, OrderDate) = 7 and dbo.GetTimePortion(OrderDate) >= @H3Begin and dbo.GetTimePortion(OrderDate) <= @H3End) Then 'H3'
  Else 'H4'
  End
)
From #Test

[sknake]

You're welcome

Please mark this thread as solved if you have found an answer to your question and good luck!