I need a conversion function, from integer to date-time

Question

Is there a conversion to go from an integer (ex: 54600) to date-time format? I am seeing the integers in a flowsheet in healthcare, used to record a time

CONVERT(varchar, DATEADD(ms, b22.meas_value * 1000, 0), 114) AS 'START TIME' : Is the code that worked in MS SQL, but SNOWFLAKE does not recognize the function.

Answer 1

You can try to use TO_DATE. The data type of the returned value is DATE.

Syntax

TO_DATE( '<integer>' )

where

<integer>
An expression that evaluates to a string containing an integer, for example ‘15000000’. Depending upon the magnitude of the string, it can be interpreted as seconds, milliseconds, microseconds, or nanoseconds. 

For details or other usage, see the above link.

Answer 2

There are a few different ways:

select 1637804567::varchar::date as date;
+------------+
| DATE       |
|------------|
| 2021-11-25 |
+------------+

select to_date(1637804567::varchar) as date;
+------------+
| DATE       |
|------------|
| 2021-11-25 |
+------------+

TO_DATE or CASTING will require the value to be string, so you need to convert the integer to string first before converting to date.

For your example, it will be like below in Snowflake:

select dateadd(ms, 54600*1000, to_date(1637804567::varchar)) as date;
+-------------------------------+
| DATE                          |
|-------------------------------|
| 2021-11-25 15:10:00.000000000 |
+-------------------------------+