Compare date?

  • Hi All,

    Is it possible to copare dete in format 10:30 AM.

    for example i want to find out the date which is in between 10:30 AM to 11:30 AM.

    Thanks

  • Are you trying to compare an actual date or just a time ?

    Can you put together some sample data ?

  • What data type did you define the column you are refering to ?

    /* Example using DateTime data type */

    Select DATEADD(HH, datediff(HH, 0, getdate()) - 1, 0) FromHH

    , DATEADD(HH, datediff(HH, 0, getdate()), 0) ToHH

    , DateTimeLastRun

    from yourschema.YourTableOrView

    where DateTimeLastRun>= DATEADD(HH, datediff(HH, 0, getdate()) - 1, 0)

    and DateTimeLastRun< DATEADD(HH, datediff(HH, 0, getdate()), 0)

    order by DateTimeLastRun ;

    /* Example using Time data type */

    Create table #ssc ( idnr int identity(1,1) not null primary key, Dt date not null default getdate() , Tm time not null default getdate() ) ;

    go

    insert into #ssc default values

    go 50

    select *

    from #ssc

    where Tm >= '09:36:18'

    and Tm < '09:36:25'

    order by Dt

    , Tm ;

    select *

    from #ssc

    where Tm >= '9:36 AM'

    and Tm < '9:37 AM'

    order by Dt

    , Tm ;

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • table structure :

    CREATE TABLE [dbo].[Orders](

    [Id] [int] NULL,

    [DateofOrd] [datetime] NULL

    )

    Table data:

    Id DateofOrd

    12013-04-17 13:00:41.847

    22013-04-17 14:00:41.847

    32013-04-17 15:00:41.847

    42013-04-17 15:10:41.847

    52013-04-17 16:10:41.847

    ;WITH ordercte(id,timevalue)as

    (

    select id,ltrim(right(convert(nvarchar(20),DateofOrd,0),8)) as timevalue from orders

    )

    select * from ordercte where timevalue between '2:00PM' AND '3:10PM'

    Result:

    Id Timevalue

    22:00PM

    33:00PM

    43:10PM

    --chalam

  • Hi,

    I am having 5 columns as below.

    Now I want to update selected data only depend upon lunch time. for example if lunch time of StuID 1 is between 1AM to 11AM then i want to set flag = 1.thus it will update 4 rows.

    StuID EffectiveDate StartTime EndTimeFlag

    12013-04-03 00:00:00.000 8:00AM 8:15AM0

    12013-04-03 00:00:00.000 8:15AM 8:30AM0

    12013-04-03 00:00:00.000 8:30AM 8:45AM0

    12013-04-03 00:00:00.000 8:45AM 9:00AM0

    12013-04-03 00:00:00.000 9:00AM 9:15AM0

    12013-04-03 00:00:00.000 9:15AM 9:30AM0

    12013-04-03 00:00:00.000 9:30AM 9:45AM0

    12013-04-03 00:00:00.000 9:45AM10:00AM0

    12013-04-03 00:00:00.00010:00AM10:15AM0

    12013-04-03 00:00:00.00010:15AM10:30AM0

    12013-04-03 00:00:00.00010:30AM10:45AM0

    12013-04-03 00:00:00.00010:45AM11:00AM0

    12013-04-03 00:00:00.00011:00AM11:15AM0

    12013-04-03 00:00:00.00011:15AM11:30AM0

    12013-04-02 00:00:00.000 1:00PM 1:15PM0

    12013-04-02 00:00:00.00012:45PM 1:00PM0

    12013-04-02 00:00:00.00012:30PM12:45PM0

    12013-04-02 00:00:00.00012:15PM12:30PM0

    12013-04-02 00:00:00.000 8:00AM 8:15AM0

    thanks

    Abhas.

  • Hi Matak,

    My Procedure is like

    ALTER PROCEDURE [dbo].[Usp_UpdateLunchTime]

    @StuID INT,

    @StartDate datetime,

    @EndDate datetime,

    @StartTime varchar(20),

    @EndTime varchar(20)

    --@LunchStart varchar(20),

    --@LunchEnd varchar(20)

    --@DayName varchar(100),

    AS

    BEGIN

    update tblTime set flag = 1

    where StuID = @StuID and EffectiveDate =@StartDate

    and StartTime between right(Convert(char(20), @StartDate,100),7) and right(Convert(char(20), @EndDate,100),7)

    --CONVERT(VARCHAR(10), EffectiveDate, 110) between CONVERT(VARCHAR(10), @StartDate, 110) and CONVERT(VARCHAR(10), @EndDate, 110)

    --exec Usp_UpdateLunchTime 1,'2013-04-01 00:00:00.000','2013-04-05 00:00:00.000','2013-04-01 07:00:00.000','2013-04-01 20:00:00.000'

    END

    Thanks

    Abhas.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply