all data not show in report after 12 am come why i need to 7 am

Hi ahmedsalah2012,



Regarding your description, the shipment that happens between 03-31-2015 8 pm to next day's 7 am should be considered belonging to 03-31-2015? If my understanding is correct, are you looking for some query as below?

DECLARE @T TABLE
(
[shipped date] DATE,
[time] VARCHAR(99),
[dateof insert] DATE
)

INSERT INTO @T VALUES
('03/31/2015','8 pm','03/31/2015'),
('03/31/2015','10 pm','03/31/2015'),
('03/31/2015','1 am','04/01/2015'),
('03/31/2015','7 am','04/01/2015')

;WITH cte AS
(
SELECT *,
DATEADD(HOUR,(CASE RIGHT([time],2) WHEN 'pm' THEN 12+LEFT([time],CHARINDEX(CHAR(32),[time])) 
                                        ELSE LEFT([time],CHARINDEX(CHAR(32),[time]))END), CONVERT(DATETIME,[dateof insert]))AS DT
FROM @T
)
,cte2 AS
(
SELECT [shipped date],
[time],
[dateof insert],
CASE WHEN CONVERT(TIME,DT) BETWEEN '00:00:00' AND '07:00:00' THEN [shipped date]
ELSE [dateof insert] END AS DT
FROM cte
)
SELECT
[shipped date],
[time],
[dateof insert],
DT
FROM cte2
WHERE --put your where clause here, filter by the DT column

If you have any question, feel free to let me know.

---

Eric Zhang

TechNet Community Support