April 17, 2013 at 8:34 am
Hi,
Can we compare AM PM date format?
Example datecolumn > 12:00PM and datecolumn < 1:00PM.
Here datecolumn is varchar. Pleae suggest any idea?
Thanks
Abhas.
April 17, 2013 at 8:55 am
Covert them into datetime & compare.
April 17, 2013 at 9:02 am
can u pls send sample code?
April 17, 2013 at 9:28 am
abhas (4/17/2013)
can u pls send sample code?
Sure, once you post your code. Of course once you do that we may ask for more, but it is a start.
April 17, 2013 at 10:07 am
Yes Lynn,
I am able to select lunch time for students using CTE as below. But not able to set flag 1 in the main student table.
DECLARE @t TABLE
(StudId INT, StartDate datetime, EndDate datetime,StartTime datetime, EndTime datetime, LunchStart datetime,LunchEnd datetime,flag bit)
INSERT INTO @t
SELECT 555,'2012-01-01 00:00:00', '2012-01-02 00:00:00','2012-01-03 07:00:00', '2012-01-03 16:00:00','2012-01-03 13:00:00','2012-01-03 14:00:00',0
;WITH Tally1 (n) AS (
SELECT TOP 100 15*(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1)
FROM sys.all_columns)
SELECT StudId,
dateadd(day, b.n-1, startdate) as effectivedate
,Tstart = RIGHT(CONVERT(VARCHAR(100),dateadd(day, b.N-1, DATEADD(mi, (a.N-1)*15, LunchStart)), 0),7)
, TsEnd = RIGHT(CONVERT(VARCHAR(100),dateadd(day, b.N-1, DATEADD(mi, a.N*15, LunchStart)), 0),7)
FROM @t
cross join Tally A
cross join Tally B
WHERE
a.N >= 1 and a.N <= datediff(mi, LunchStart, LunchEnd)/15 and
b.N >= 1 and b.N <= DATEDIFF(day, startdate,enddate) + 1
ORDER BY effectivedate
April 17, 2013 at 11:15 am
Not sure what you are expecting but running the following all I see in the output is 1:00 PM to 2:00 PM which corresponds to the LunchStart and LunchEnd for the sample student:
DECLARE @t TABLE
(StudId INT, StartDate datetime, EndDate datetime,StartTime datetime, EndTime datetime, LunchStart datetime,LunchEnd datetime,flag bit);
INSERT INTO @t
SELECT 555,'2012-01-01 00:00:00', '2012-01-02 00:00:00','2012-01-03 07:00:00', '2012-01-03 16:00:00','2012-01-03 13:00:00','2012-01-03 14:00:00',0;
WITH Tally1 (n) AS (
SELECT TOP 100
15 * (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1)
FROM
sys.all_columns
)
SELECT
StudId, LunchStart, LunchEnd,
dateadd(day, b.n-1, startdate) as effectivedate,
a.n,
b.n,
datediff(mi, LunchStart, LunchEnd)/15,
DATEDIFF(day, startdate,enddate) + 1,
Tstart = RIGHT(CONVERT(VARCHAR(100),dateadd(day, b.N-1, DATEADD(mi, (a.N-1)*15, LunchStart)), 0),7),
TsEnd = RIGHT(CONVERT(VARCHAR(100),dateadd(day, b.N-1, DATEADD(mi, a.N*15, LunchStart)), 0),7)
FROM @t
cross join Tally A
cross join Tally B
WHERE
a.N >= 1 and a.N <= datediff(mi, LunchStart, LunchEnd)/15 and
b.N >= 1 and b.N <= DATEDIFF(day, startdate,enddate) + 1
ORDER BY effectivedate ;
By the way, CTE's don't start with a semicolon (;). The semicolon is a statement TERMINATOR not a statement BEGININATOR and belongs on the statement immediately preceding the WITH of a CTE declaration.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply