New post
Question
· Oct 20, 2020

Convert Epoch Time in MS to smalldatetime to perform COUNT

#SQL #Caché

Hey, 

I have a value in epoch time in which I need convert in order to be able to lookup a table that has dates in YYYY-MM-DD HH:MM:SS format

I thought the below would work but it is not.

select COUNT ('Arrival Time') FROM dbo.table where 'Arrival Time' < DATEADD(ms, CONVERT(int,LEFT(1603173432000, 20)), '1970-01-01 00:00:00')

Discussion (8)0
Log in or sign up to continue
Julius Kavay · Oct 21, 2020

I'm pretty sure, you intended to write

select COUNT (Arrival Time) FROM dbo.table where Arrival Time < DATEADD(ms, CONVERT(int,LEFT(1603173432000, 20)), '1970-01-01 00:00:00')

i.e. do not place propertynames under quote

By the way, I don't know, how many records you have in your table, but if you have thousands or millions of records, consider to compute constant things just one time!

DATEADD(ms, CONVERT(int,LEFT(1603173432000, 20)), '1970-01-01 00:00:00')

is a constant value, it's not neccessary to compute it for each record! Also, in this particular case, CONVERT() and LEFT() are also not needless, 1603173432000 is the value, and it is an integer.

0 0
Daniel Buxton · Oct 21, 2020

Hey Julius, 

I intended to write 

select COUNT ("Arrival Time") FROM dbo.table where "Arrival Time" < DATEADD(ms, CONVERT(int,LEFT(1603173432000, 20)), '1970-01-01 00:00:00')

However it is returning a count of 0.  1603173432000 being 2020-10-21 13:00:00 so there should be a COUNT of 2

Arrival Time
2020-10-21 12:50:05
2020-10-21 12:57:56
2020-10-21 13:42:11

It is just testing ATM but I dont epect the table to be more than 100 rows as old records get deleted.

0 0
Daniel Buxton · Oct 26, 2020

Hey All, 

I will close this post.  Ended tweaking my application so instead of using a webservice to initially get the time and date in epoch, I used select now().  So my application now works as per below, it automatically deals with DST and epoch conversion to the required datetime format.

var1 = select now()

select COUNT ("Arrival Time") FROM dbo.table where "Arrival Time" < var1
0 0