Increment date based on the time value automatically

  • I have a client data which has the candidate id, a start date which will have only the date in varchar format and a time value associated to that date in varchar format in a seperate column.

    To give a brief explanation about the data a candidate will come to study center at any time point in a day for example 10:00 AM on 20-10-2014. The start date will be 20-10-2014 and from 10:00 AM he will have test based on the time point. So if the time point is 2 HR, then at 12:00 PM he will have a test. If the time point is 8 HR, it will get added with 10:00 AM and based on this he will have the test at 06:00 PM. When the timepoint reaches 00:00 the start date needs to be the next date instead of 20-10-2014.

    The start date needs to be appended with the time value such that when it crosses over the time 00:00, the start date needs to get increased by 1 ie the next day. I have added the start date to the time by using the code below

    CAST(STARTDATE as datetime) + CAST(CAST(STUFF([TIME],3,0,':') as time(0)) as datetime) as [EXPECTEDDATETIME]

    By this above code i have created the expected datetime however

    I will not be able to hardcode the value due to dynamic data. I tried to apply >= and < to the time value something like

    case when MyTime >= '00:00' and MyTime < '08:10' the Dateadd(day, 1, date)

    This works perfect but my concern is that I cannot put the value 08:10 because it will not a constant value for all rows.

    I have provided a screenshot of my data and a expected date column for reference.

    Candidate StartDateStartTimeExpected DateTime Timepoint

    1 20141020 1000 2014-10-20 10:00:00 0 HR

    1 201410201200 2014-10-20 12:00:00 02 HR

    1 201410201400 2014-10-20 14:00:00 04 HR

    1 201410201800 2014-10-20 18:00:00 08 HR

    1 201410200000 2014-10-21 00:00:00 12 HR

    1 201410201200 2014-10-21 12:00:00 24 HR

    2 20141020 1100 2014-10-20 11:00:00 0 HR

    2 201410201300 2014-10-20 13:00:00 02 HR

    2 201410201500 2014-10-20 15:00:00 04 HR

    2 201410201900 2014-10-20 19:00:00 08 HR

    2 201410202100 2014-10-20 21:00:00 12 HR

    2 201410202300 2014-10-20 23:00:00 24 HR

    2 201410200230 2014-10-21 02:30:00 27 HR

    2 201410201330..............

    3 201410261530...............

    3 201410262000

    3 20141026 0930

    3 20141026 1020

    3 20141026 1120

    I have also attached the data for reference.

    Can anyone help me in this request?

  • Can you double check your expected datetime against the left column, some data does not look correct.

    Also, what does your initial attempt at coding this look like?

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • why does 20141026 need to be converted 2014-10-21?

    do you have any ID column that defines the order of data in your table?

    please confirm that is defintely a SQL 2012 version

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Hi All,

    I updated the data to reflect the correct ones

  • Hi,

    Its a type mistake... I have updated the data...

    I am using sql 2012...

  • declare @i int,@T smallint

    select @i = 20100101

    Select @T=1900

    select dateadd(minute,@T%100,(dateadd(hour,@T/100,

    CONVERT (datetime,convert(char(8),@i))

    )))

    try this

    Edited to say that I am now confused so I am leaving initial attempt in here, but might not work anymore.

    Thought we were trying to concatenate int date and int time into a date time.

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • Hi,

    Its not working

    i tried with the date as 20101020 and time as 0030.. below is the code

    declare @i int,@T smallint

    select @i = 20101020

    Select @T=0030

    select dateadd(minute,@T%100,(dateadd(hour,@T/100,

    CONVERT (datetime,convert(char(8),@i))

    )))

    However i am getting the result as 2010-10-20 00:30:00.000

  • karthik82.vk (7/1/2015)


    I have a client data which has the candidate id, a start date which will have only the date in varchar format and a time value associated to that date in varchar format in a seperate column.

    To give a brief explanation about the data a candidate will come to study center at any time point in a day for example 10:00 AM on 20-10-2014. The start date will be 20-10-2014 and from 10:00 AM he will have test based on the time point. So if the time point is 2 HR, then at 12:00 PM he will have a test. If the time point is 8 HR, it will get added with 10:00 AM and based on this he will have the test at 06:00 PM. When the timepoint reaches 00:00 the start date needs to be the next date instead of 20-10-2014.

    The start date needs to be appended with the time value such that when it crosses over the time 00:00, the start date needs to get increased by 1 ie the next day. I have added the start date to the time by using the code below

    CAST(STARTDATE as datetime) + CAST(CAST(STUFF([TIME],3,0,':') as time(0)) as datetime) as [EXPECTEDDATETIME]

    By this above code i have created the expected datetime however

    I will not be able to hardcode the value due to dynamic data. I tried to apply >= and < to the time value something like

    case when MyTime >= '00:00' and MyTime < '08:10' the Dateadd(day, 1, date)

    This works perfect but my concern is that I cannot put the value 08:10 because it will not a constant value for all rows.

    I have provided a screenshot of my data and a expected date column for reference.

    Candidate StartDateStartTimeExpected DateTime Timepoint

    1 20141020 1000 2014-10-20 10:00:00 0 HR

    1 201410201200 2014-10-20 12:00:00 02 HR

    1 201410201400 2014-10-20 14:00:00 04 HR

    1 201410201800 2014-10-20 18:00:00 08 HR

    1 201410200000 2014-10-21 00:00:00 12 HR

    1 201410201200 2014-10-21 12:00:00 24 HR

    2 20141020 1100 2014-10-20 11:00:00 0 HR

    2 201410201300 2014-10-20 13:00:00 02 HR

    2 201410201500 2014-10-20 15:00:00 04 HR

    2 201410201900 2014-10-20 19:00:00 08 HR

    2 201410202100 2014-10-20 21:00:00 12 HR

    2 201410202300 2014-10-20 23:00:00 24 HR

    2 201410200230 2014-10-21 02:30:00 27 HR

    2 201410201330..............

    3 201410261530...............

    3 201410262000

    3 20141026 0930

    3 20141026 1020

    3 20141026 1120

    I have also attached the data for reference.

    Can anyone help me in this request?

    The expected result cannot be achieved from the initial data, as there is no information to indicate the order in which the data has arrived. Any attempt to use windowed functions would need to know the original order of the records, and you haven't indicated that such information exists. Once data is in a SQL Server table, that original order is lost and gone forever, unless there is a column that gets populated with that information at the time the data is imported. Try and use an ORDER BY clause on this table and get the records to appear in their original order, and you'll see what I mean. Let us know if that changes...

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • So, why the new thread for this topic. This is really just a duplicate of this thread with more information (but not enough).

  • Hi All,

    I found the solution...

    ;WITH cte

    AS (

    -- take the first row

    SELECT Id ,

    Candidate ,

    MyDate ,

    MyTime

    FROM #TempFormatted

    WHERE Id = 1

    -- add next row (look at join: t.Id = cte.Id + 1)

    UNION ALL

    SELECT t.Id ,

    t.Candidate ,

    -- CASE used to compare rows and add a DAY if required

    CASE WHEN t.MyTime > cte.MyTime

    AND t.Candidate = cte.Candidate

    THEN cte.MyDate

    WHEN t.MyTime < cte.MyTime

    AND t.Candidate = cte.Candidate

    THEN DATEADD(DAY, 1, t.MyDate)

    ELSE t.MyDate

    END AS MyDate ,

    t.MyTime

    FROM cte

    INNER JOIN #TempFormatted t ON t.Id = cte.Id + 1

    )

    -- output from cte

    SELECT cte.Id ,

    cte.Candidate ,

    cte.MyDate ,

    cte.MyTime ,

    CONVERT(DATETIME, cte.MyDate) + CONVERT(DATETIME, cte.MyTime) FormattedValue

    FROM cte

  • karthik82.vk (7/1/2015)


    Hi All,

    I found the solution...

    ;WITH cte

    AS (

    -- take the first row

    SELECT Id ,

    Candidate ,

    MyDate ,

    MyTime

    FROM #TempFormatted

    WHERE Id = 1

    -- add next row (look at join: t.Id = cte.Id + 1)

    UNION ALL

    SELECT t.Id ,

    t.Candidate ,

    -- CASE used to compare rows and add a DAY if required

    CASE WHEN t.MyTime > cte.MyTime

    AND t.Candidate = cte.Candidate

    THEN cte.MyDate

    WHEN t.MyTime < cte.MyTime

    AND t.Candidate = cte.Candidate

    THEN DATEADD(DAY, 1, t.MyDate)

    ELSE t.MyDate

    END AS MyDate ,

    t.MyTime

    FROM cte

    INNER JOIN #TempFormatted t ON t.Id = cte.Id + 1

    )

    -- output from cte

    SELECT cte.Id ,

    cte.Candidate ,

    cte.MyDate ,

    cte.MyTime ,

    CONVERT(DATETIME, cte.MyDate) + CONVERT(DATETIME, cte.MyTime) FormattedValue

    FROM cte

    please give credit where credit is due

    http://stackoverflow.com/questions/31157104/increment-date-based-on-the-time-value-automatically-in-sql

    you will note that the solution requires an ID column to order the data....which you had previously been asked about on this forum

    anyways...pleased you have a solution

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Hi J,

    The reply I got from the Stackoverflow forum helped me to get the solution but not the answer for my scenario. In the answer I got Tanner has included a column but in my situation I cannot have a id column as i will be having incremental data which keeps on updating and changing.

    So What I did is I used the ROW_NUMBER OVER ORDER BY instead of the id column and created a view that will help me to change the dates based on the time.

    Then I used that view with my few other tables to get some values pivoted and got the solution.

    Anyhow the reply of Tanner has helped me solved the issue.

    Thanks again to Tanner.

  • karthik82.vk (7/2/2015)


    Hi J,

    The reply I got from the Stackoverflow forum helped me to get the solution but not the answer for my scenario. In the answer I got Tanner has included a column but in my situation I cannot have a id column as i will be having incremental data which keeps on updating and changing.

    So What I did is I used the ROW_NUMBER OVER ORDER BY instead of the id column and created a view that will help me to change the dates based on the time.

    Then I used that view with my few other tables to get some values pivoted and got the solution.

    Anyhow the reply of Tanner has helped me solved the issue.

    Thanks again to Tanner.

    kudos to you for your response......will be appreciated I am sure

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

Viewing 13 posts - 1 through 12 (of 12 total)

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