none
formula to account for next day when hour change falls into next day RRS feed

  • Question

  • Hi,

    I'm in need of a formula that would account for next day change. I am working on a view that adds 2 hours (due to changing time zone) to the "create date". However, if adding the 2 hours to the create date falls into the next day, the DATE does NOT change. How can I account for that? Im using

    dateadd(hh,2)

    to add the date, but I'm not sure how to account for the next date if the time falls into that.

    Friday, July 19, 2019 7:22 PM

Answers

  • -- Replace 20190717 with date_no_conv, 20500 with time_no_conv
    SELECT 
    	CAST('20190717' AS date) AS [CREATE_DATE], 
    	LEFT(RIGHT('000000' + CAST(20500 AS varchar(6)), 6), 2) + ':' + SUBSTRING(RIGHT('000000' + CAST(20500 AS varchar(6)), 6), 3, 2) + ':' + RIGHT(RIGHT('000000' + CAST(20500 AS varchar(6)), 6), 2) AS [CREATE_TIME],
    	CAST('20190717' AS datetime) + CAST(LEFT(RIGHT('000000' + CAST(20500 AS varchar(6)), 6), 2) + ':' + SUBSTRING(RIGHT('000000' + CAST(20500 AS varchar(6)), 6), 3, 2) + ':' + RIGHT(RIGHT('000000' + CAST(20500 AS varchar(6)), 6), 2) AS datetime) AS [CREATE_DATE_TIME],
    	DATEADD(hh, 2, CAST('20190717' AS datetime) + CAST(LEFT(RIGHT('000000' + CAST(20500 AS varchar(6)), 6), 2) + ':' + SUBSTRING(RIGHT('000000' + CAST(20500 AS varchar(6)), 6), 3, 2) + ':' + RIGHT(RIGHT('000000' + CAST(20500 AS varchar(6)), 6), 2) AS datetime)) AS [CREATE_DATE_TIME_PLUS_TWO_HOURS],
    	CAST(DATEADD(hh, 2, CAST('20190717' AS datetime) + CAST(LEFT(RIGHT('000000' + CAST(20500 AS varchar(6)), 6), 2) + ':' + SUBSTRING(RIGHT('000000' + CAST(20500 AS varchar(6)), 6), 3, 2) + ':' + RIGHT(RIGHT('000000' + CAST(20500 AS varchar(6)), 6), 2) AS datetime)) AS date) AS [CREATE_DATE_PLUS_TWO_HOURS],
    	CAST(DATEADD(hh, 2, CAST('20190717' AS datetime) + CAST(LEFT(RIGHT('000000' + CAST(20500 AS varchar(6)), 6), 2) + ':' + SUBSTRING(RIGHT('000000' + CAST(20500 AS varchar(6)), 6), 3, 2) + ':' + RIGHT(RIGHT('000000' + CAST(20500 AS varchar(6)), 6), 2) AS datetime)) AS time) AS [CREATE_TIME_PLUS_TWO_HOURS]
    --FROM YourView


    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by uahmed114 Friday, July 19, 2019 9:47 PM
    Friday, July 19, 2019 9:13 PM

All replies

  • Can you post a few time sample and what are you expecting from adding two hours to them? Thanks.
    Friday, July 19, 2019 7:34 PM
    Moderator
  • If your column is a datetime, it should automatically give you the next date. See below:

    declare @d datetime
    set @d = '2019-07-19 23:00:00.000'
    
    select dateadd(hh,2, @d)

    Friday, July 19, 2019 7:36 PM
  • here is the code that is currently adding 2 hours to the time:

    LEFT(cast(dateadd(hh,2,LEFT(RIGHT(CONCAT('000000', CREATE_TIME),6), 2)+':'+SUBSTRING(RIGHT(CONCAT('000000', CREATE_TIME), 6), 3, 2)+':'+RIGHT(RIGHT(CONCAT('000000', CREATE_TIME), 8), 2)) AS TIME),8)

    Friday, July 19, 2019 7:45 PM
  • what if I dont know what the time is? Will it be able to automatically add the day if the time falls into next day?
    Friday, July 19, 2019 7:46 PM
  • Basically, current code adds 2 hours to the time. I want it to be able to affect the DAY as well, say if the create_time is 11pm, adding two hours makes it fall into the next day, 1am. The time is added correctly, but how can I affect that change into the date? How do I say, "if( HH+2 >=24 (hours), then +1 day)". 
    Friday, July 19, 2019 7:48 PM
  • The function dateadd needs three parameter and you posted a wrong one.

    dateadd(hh,2,'2019-07-19 23:00:00.000' )

    --will return:

    2019-07-20 01:00:00.000

    You should use a datetime datatype for the column if you want it to hand both date and time.

    If your column is a time data type, you will have this problem.

    Friday, July 19, 2019 7:57 PM
    Moderator
  • What is the data type of the column CREATE_TIME? You need to combine the date and time to datatime and then add 2 hours. If you need to seperate the date and time after adding 2 hours, you have to split datetime to date and time.

    A Fan of SSIS, SSRS and SSAS

    Friday, July 19, 2019 8:02 PM
  • CURRENTLY, they are both separate columns (date, time). When I try to combine them, I get an Arithmetic overflow error. I'm not sure of the datatype of the Create_Time, as I'm using a view, and this data is coming from an old database via OPENQUERY. 

    I came up with a formula, but this isn't working yet. Maybe this helps give you an idea of what I'm looking for. 

    LEFT(cast(dateadd(hh,2,LEFT(RIGHT(CONCAT('000000', CREATE_TIME),6), 2)+':'+SUBSTRING(RIGHT(CONCAT('000000', CREATE_TIME), 6), 3, 2)+':'+RIGHT(RIGHT(CONCAT('000000', CREATE_TIME), 8), 2)) AS TIME),8)AS CREATE_TIME,
    				 
    CASE WHEN CREATE_TIME < DATEPART(HH,24)
    THEN CREATE_DATE
    WHEN CREATE_TIME >= DATEPART(HH,24)
    THEN DATEADD(DD,1,CREATE_DATE)
    ELSE CREATE_DATE
    END AS ADJ_CREATE_DATE,

    Friday, July 19, 2019 8:10 PM
  • DECLARE @d date = '2019-07-19';
    DECLARE @t time = '16:10';
    SELECT CAST(@d AS datetime) + CAST(@t AS datetime)


    A Fan of SSIS, SSRS and SSAS

    Friday, July 19, 2019 8:20 PM
  • If your table which is used for your view has a time datatype along with a date column (it is a wrong design),

    You need to create a datetime value based on these two columns and use formula to add two hours. Then split your datetime with added two hours into date and time.

    Post a few row's data from these two columns so we can help you to get a query you can use in your view. 

    Without sample data for your date and time, we cannot get a working sample for you.

    Friday, July 19, 2019 8:21 PM
    Moderator
  • should I do this: DECLARE @d date = CREATE_DATE
    DECLARE @t time = CREATE_TIME

    Friday, July 19, 2019 8:27 PM

  • SELECT CREATE_DATE, CREATE_TIME, CAST(CREATE_DATE AS datetime) + CAST(CREATE_TIME AS datetime)
    FROM YourView


    A Fan of SSIS, SSRS and SSAS

    Friday, July 19, 2019 8:33 PM
  • date_no_conv	time_no_conv	CREATE_DATE	CREATE_TIME
    20190717	20500	7/17/2019	4:05:00
    20190717	20500	7/17/2019	4:05:00
    20190717	20500	7/17/2019	4:05:00
    20190717	20500	7/17/2019	4:05:00
    20190717	20500	7/17/2019	4:05:00
    20190717	20500	7/17/2019	4:05:00
    20190717	20500	7/17/2019	4:05:00
    

    above is the result that im getting. The first 2 columns displays data without any formating/conversion done. Straight from db.

    The second two columns are displaying the data after the conversions. 

    Below are the two conversions that I'm doing do display the data as above:

    CAST(CONVERT(VARCHAR, CREATE_DATE) AS DATE) AS CREATE_DATE,
    
    				
    LEFT(cast(dateadd(hh,2,LEFT(RIGHT(CONCAT('000000', CREATE_TIME),6), 2)+':'+SUBSTRING(RIGHT(CONCAT('000000', CREATE_TIME), 6), 3, 2)+':'+RIGHT(RIGHT(CONCAT('000000', CREATE_TIME), 8), 2)) AS TIME),8)AS CREATE_TIME

    Now I need to somehow combine these columns into one, and then add 2 hours, and if the addition causes the TIME to fall over to the next day, then the DATE must also change to the NEXT DAY. 

    YOUR HELP IS VERY MUCH APPRECIATED.

    Friday, July 19, 2019 8:38 PM
  • I get the following error: Arithmetic overflow error converting expression to data type datetime.
    Friday, July 19, 2019 8:45 PM
  • Also, I found out the data types from the original table are DATE for the date col, and NUMBER for the TIME column
    Friday, July 19, 2019 8:46 PM
  • 20500 means 02:05:00?

    A Fan of SSIS, SSRS and SSAS


    • Edited by Guoxiong Friday, July 19, 2019 8:54 PM
    Friday, July 19, 2019 8:54 PM
  • YES. +2 HOURS SO 4:05 is the result.
    Friday, July 19, 2019 8:57 PM
  • -- Replace 20190717 with date_no_conv, 20500 with time_no_conv
    SELECT 
    	CAST('20190717' AS date) AS [CREATE_DATE], 
    	LEFT(RIGHT('000000' + CAST(20500 AS varchar(6)), 6), 2) + ':' + SUBSTRING(RIGHT('000000' + CAST(20500 AS varchar(6)), 6), 3, 2) + ':' + RIGHT(RIGHT('000000' + CAST(20500 AS varchar(6)), 6), 2) AS [CREATE_TIME],
    	CAST('20190717' AS datetime) + CAST(LEFT(RIGHT('000000' + CAST(20500 AS varchar(6)), 6), 2) + ':' + SUBSTRING(RIGHT('000000' + CAST(20500 AS varchar(6)), 6), 3, 2) + ':' + RIGHT(RIGHT('000000' + CAST(20500 AS varchar(6)), 6), 2) AS datetime) AS [CREATE_DATE_TIME],
    	DATEADD(hh, 2, CAST('20190717' AS datetime) + CAST(LEFT(RIGHT('000000' + CAST(20500 AS varchar(6)), 6), 2) + ':' + SUBSTRING(RIGHT('000000' + CAST(20500 AS varchar(6)), 6), 3, 2) + ':' + RIGHT(RIGHT('000000' + CAST(20500 AS varchar(6)), 6), 2) AS datetime)) AS [CREATE_DATE_TIME_PLUS_TWO_HOURS],
    	CAST(DATEADD(hh, 2, CAST('20190717' AS datetime) + CAST(LEFT(RIGHT('000000' + CAST(20500 AS varchar(6)), 6), 2) + ':' + SUBSTRING(RIGHT('000000' + CAST(20500 AS varchar(6)), 6), 3, 2) + ':' + RIGHT(RIGHT('000000' + CAST(20500 AS varchar(6)), 6), 2) AS datetime)) AS date) AS [CREATE_DATE_PLUS_TWO_HOURS],
    	CAST(DATEADD(hh, 2, CAST('20190717' AS datetime) + CAST(LEFT(RIGHT('000000' + CAST(20500 AS varchar(6)), 6), 2) + ':' + SUBSTRING(RIGHT('000000' + CAST(20500 AS varchar(6)), 6), 3, 2) + ':' + RIGHT(RIGHT('000000' + CAST(20500 AS varchar(6)), 6), 2) AS datetime)) AS time) AS [CREATE_TIME_PLUS_TWO_HOURS]
    --FROM YourView


    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by uahmed114 Friday, July 19, 2019 9:47 PM
    Friday, July 19, 2019 9:13 PM
  • Here is an example you can work from. I'm using variables to show the principle (and to make it easy to test). The is key is that I use integer division and the modulus operator to crack the numeric time value into seconds which I add to the date.

    DECLARE @d date = '20170719', @t int = 231123

    ; WITH datetime AS (
       SELECT dateadd(ss, @t/10000*3600 + (@t/100 % 100) * 60 + @t % 100, convert(datetime2(0), @d)) AS datetime
    ), addhours AS (
       SELECT dateadd(HOUR, 2, datetime) AS twohoursmore
       FROM   datetime
    )
    SELECT @d = convert(date, twohoursmore),        @t = datepart(hour, twohoursmore) * 10000 + datepart(minute, twohoursmore) * 100 +             datepart(second, twohoursmore)
    FROM  addhours
    SELECT @d, @t


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, July 19, 2019 9:46 PM
  • THank you for all your help, everyone! I figured out my answer, and Guoxiong's last post was the one that helped me most. Thanks!!
    Friday, July 19, 2019 9:49 PM
  • create table test (date_no_conv char(8),	time_no_conv int)
    
    insert into test values ('20190717',20500)
    ,('20190717',220500)
    ,('20191231',230500)
    
    ;with mycte as
    (
    select date_no_conv, time_no_conv,
    Dateadd(hour,2,msdb.dbo.Agent_datetime(date_no_conv, time_no_conv)) yourNewDatetime
    ,msdb.dbo.Agent_datetime(date_no_conv, time_no_conv) [Run_Datetime] 
    from test)
    
    Select *
    ,FORMAT(yourNewDatetime,'M/dd/yyyy') CREATE_DATE 
    ,FORMAT(yourNewDatetime,'h/mm/ss') CREATE_TIME
    from mycte
    
    
    drop table test

    Monday, July 22, 2019 2:29 PM
    Moderator