# How to get last 6 months data from createdutc column

• ### 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

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

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
• ```;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))```

Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog

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))```

Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog

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