can we compare time AM/PM time format?

  • 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.

  • Covert them into datetime & compare.

  • can u pls send sample code?

  • 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.

  • 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

  • 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