Post reply

Update variable having timestamp value in it.

  • June 23, 2016 at 6:05 am

    #317362

    Hi everyone,

    I am writing one T-SQL code where i have below requirement ,

    suppose a variable contains timstamp value like

    var_time = 'Apr 14, 2016 3:21:39.0 PM'

    could you please help me how can i update the timestamp value

    from 'Apr 14, 2016 3:21:39.0 PM' to 'Apr 14, 2016 3:00:00.0 PM' like

    original value var_time = 'Apr 14, 2016 3:21:39.0 PM'

    required value var_time = 'Apr 14, 2016 3:00:00.0 PM'

    Best Regards,

    Abhi

  • June 23, 2016 at 6:17 am

    #1886497

    DECLARE @var_time datetime2(1) = '20160414 15:21:39.0';

    SELECT @var_time, DATEADD(hour, DATEDIFF(hour, '19000101', @var_time), CAST('19000101' AS datetime2(1)));

  • June 23, 2016 at 7:45 am

    #1886530

    It's not clear if your requirement is to round to the nearest hour or just round down or something else?

    If you need to round down or up based on minutes might I suggest something like this...

    DECLARE @MyDates TABLE (Dates DATETIME)

    INSERT INTO @MyDates VALUES ('2016-04-14 15:21:39'), ('2016-04-14 15:41:39')

    SELECT

    CASE WHEN (DATEDIFF(mi, 0, Dates) % 60) < 30

    THEN DATEADD(hour, DATEDIFF(hour, 0, Dates), 0)

    ELSE DATEADD(hour, DATEDIFF(hour, 0, Dates) + 1, 0) END AS RoundedDate

    FROM

    @MyDates

    SELECT quote FROM brain WHERE original = 1
    0 rows returned

  • July 14, 2016 at 1:58 am

    #1890014

    Apologies for being late ,

    my requirement is i am taking min of snapshot date from one of my table .

    for ex :

    select Min (Snapshot_date) from table.

    Suppose output will be like 'Dec 30, 2015 12:07:48.0 AM'

    but i would like to convert the output to 'Dec 30, 2015 12:00:00.0 AM'

    Please do let me know how to convert it .

    Best Regards,

    Abhi

Viewing 4 posts - 1 through 3 (of 3 total)

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