Convert Epoch Time in MS to smalldatetime to perform COUNT
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')
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.
Property Arrival Time has a whitespace in the name so it must be quoted.
Sorry, I didn't noticed the space char... maybe I need new glasses ;-)
But yes, if you have some unusual property names, then you need double-quotes ($char(34), he used $char(39))
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 2It is just testing ATM but I dont epect the table to be more than 100 rows as old records get deleted.Why?
Your solution seems to work
Oh yes, DATEADD(ms, ....) works, but
select 'Arrival Time' ...
won't work. This gives the string constant of "Arrival Time"
oh yeah getting closer. It looks like the webservice I am using is actually sending me UTC Time and I need to convert to my timezone
select DATEADD(ms, CONVERT(string,LEFT(1603274296000+39600000, 20)), '1970-01-01 00:00:00')adds 11 hours and shows my current time of 2020-10-21 20:58:16Hey 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