How to Convert a Timestamp (Date Format) to Bigint in SQL

date or string type to bigint

What Gordon said.

If you have Javascript timestamps, keep in mind that they are simply the number of milliseconds since 1970-01-01T00:00:00.000Z in 64-bit floating point. They can be converted to BIGINT easily. If you're storing those timestamps in DATETIME(3) or TIMESTAMP(3) data types, use UNIX_TIMESTAMP(date)*1000 to get a useful BIGINT millisecond value.

If you only care about dates (not times) you can use TO_DAYS() to get an integer number of days since 0000-01-01 (in the Gregorian calendar; if you're an historian of antiquity and care about the Julian calendar, this approach has problems. If you don't know what I'm talking about, you don't need to worry.) But INT will suffice for these day numbers; BIGINT is overkill.

SQL: Convert bigint type to formatted date

Assuming that your number is an epoch timestamp in milliseconds (that is, the number of milliseconds since January 1st, 1970), you can use from_unixtime():

select from_unixtime(1601625689496 / 1000)

This gives you a datetime value. If you want to drop the time component, then:

select date(from_unixtime(1601625689496 / 1000))

Note that 1601625689496 actually maps to 2020-10-02, not 2020-10-04.

Convert BigInt timestamp to just Date in SQL

Why not just take the 8 left most characters, and convert it?

SELECT CONVERT(date, LEFT(20170609043000,8));

Ideally though, you should be using a date and time datatype to store your date and time data. Storing them in a different data type only ends up causing problems and never solves a problem that can't be solved else where (aka your presentation layer).

How to convert BIGINT to TIMESTAMPZ in a query?

You have to use the single-argument form of to_timestamp:

SELECT to_timestamp(1644853209.6);

       to_timestamp       
══════════════════════════
 2022-02-14 16:40:09.6+01
(1 row)

How to format SQL date timestamp from Bigint to C# date

You need to use the DateTime ParseExact method e.g.:

var date = DateTime.ParseExact("20220504112601755", "yyyyMMddhhmmssfff", System.Globalization.CultureInfo.InvariantCulture);

Convert Bigint to Datetime SQL Server

For your example you would need to do something like this.

select convert(datetime, convert(char(8), 20190108))

I can't for the life of me figure out what you are trying to do with your DATEADD logic there.

Conversion of bigint to date only in SQL Server?

You can use CAST to DATE like following.

SELECT Cast(Dateadd(second, Cast(sr.executed_on AS BIGINT) / 1000, '19700101') 
            AS DATE) 
       AS ExecutedOn 
FROM   table1 

---

Related Topics