none
How to get last 6 months data from createdutc column RRS feed

  • Question

  • Hi,

    I have a table name BtPrintJobs where i want to return last 6 months data.

    I have tried the following way, 

    select top 3 * from BtPrintJobs

    results: 

    CreatedUTC
    636482124361400000
    636482118335560000
    636482118264740000

    i ran this following query to get last utc 

    select MIN(CreatedUTC) from BtPrintJobs 

    Declare @TickValue bigint
    Declare @Days float


    Set @TickValue = 635283186097710000
    Select @Days = @TickValue * POWER(10.00000000000,-7) / 60 / 60 / 24


    Select DATEADD(d, Cast(@Days As int), Cast('0001-01-01' As DATE)) 
        + Cast( (@Days - FLOOR(@Days)) As DateTime)

    where i get the date information 2013-11-07

    Please help!


    Zahid

    Friday, December 8, 2017 2:41 AM

All replies

  • Well, simply doing the inverse would actually put now at around 63648385277 * 10^7

    So 6 months ago will be around 63632476800 so anything greater than ~63632476800 would be last 6 months

    Friday, December 8, 2017 3:25 AM
  • Hi Zahid,

    Do you mean to want this?

    create function fn_get_utc_date
    (@TickValue bigint
    )
    RETURNS datetime
    AS
    begin
     declare @Days decimal(18,5)
     declare @utc_date datetime
     set @Days = @TickValue * POWER(10.00000000000,-7) / 60 / 60 / 24
     set @utc_date=dateadd(s,cast((@Days - FLOOR(@Days))*24*3600 as int), cast(DATEADD(d, Cast(@Days As int), Cast('0001-01-01' As DATE)) as datetime)) 
    
     return @utc_date
    
    end
    
    create table  BtPrintJobs 
    (
    CreatedUTC bigint
    )
    
    insert into BtPrintJobs values
    (636482124361400000),
    (636482118335560000),
    (636482118264740000),
    (635283186097710000)
    
    ;with cte as 
    (
    select CreatedUTC,dbo.fn_get_utc_date(CreatedUTC) as actual_utc from BtPrintJobs
    )
    select 
    CreatedUTC,
    actual_utc
    from cte 
    where actual_utc>=dateadd(month,-6,getdate())

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 8, 2017 3:41 AM
    Moderator
  • ;With CTE
    as
    (
    SELECT *,(CreatedUTC * POWER(10.0,-7))/60 * 60 * 24 AS UTCDt
    FROM Table
    )
    SELECT
    FROM CTE
    WHETE UTCDt >= DATEADD(mm,-6,CAST(GETUTCDATE() as date))


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, December 8, 2017 3:53 AM
  • Will,

    Thanks for your clarification, but i need last 6 months data from btprintjobs table.

    user requested me 

    "I need info from the database about some labels that were printed in the last 6 months, but with the state of the database the Bartender utility is struggling to retrieve data without timing out. Is there any way to directly query for data to retrieve records needed to solve a business question?"





    Zahid

    Friday, December 8, 2017 4:00 AM
  • Will,

    Thanks for your clarification, but i need last 6 months data from btprintjobs table.

    user requested me 

    "I need info from the database about some labels that were printed in the last 6 months, but with the state of the database the Bartender utility is struggling to retrieve data without timing out. Is there any way to directly query for data to retrieve records needed to solve a business question?"





    Zahid

    this?

    ;With CTE
    as
    (
    SELECT *,(CreatedUTC * POWER(10.0,-7))/60 * 60 * 24 AS UTCDt
    FROM btprintjobs
    )
    SELECT
    FROM CTE
    WHETE UTCDt >= DATEADD(mm,-6,CAST(GETUTCDATE() as date))


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, December 8, 2017 4:29 AM
  • Will,

    Thanks for your clarification, but i need last 6 months data from btprintjobs table.

    Zahid

    Hi Zahid,

    I am not quite clear about the meaning of the message "i need last 6 months data from btprintjobs table", the following WHERE condition statement is not equal to the meaning of "last 6 months data ", is it?

    where actual_utc>=dateadd(month,-6,getdate())

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 8, 2017 4:33 AM
    Moderator