CODEWITHSUNDEEP
wordpresstype-conversionsql-server-2016
Harry
Harry

Reputation: 2941

wordpress integer date to date

I have a wordpress sql database that records CREATE_DATE in an integer format

some examples are

1615776024
1615852620
1616024968
1616027330
1616981834

some of the entries were created this month, so the format in which the date is recorded doesn't reveal anything about itself.

I have tried these but all of these statements error

SELECT

CONVERT(DATE,CONVERT(CHAR(8),CREATE_DATE),112)
,CONVERT(DATE,CONVERT(VARCHAR(10), CREATE_DATE))
,CONVERT(DATETIME, CONVERT(VARCHAR(10), CREATE_DATE))

FROM WORDPRESS_TABLE

But they all error with the same error message :

Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from character string.

can any one tell me how I can convert this into the following format please? dd/MM/yyyy in SQL?

Upvotes: 0

Views: 265

Answers (1)

Niels Berglund
Niels Berglund

Reputation: 1698

Having thought about it a bit more, (like 10 seconds); I wonder if what you see is not epoch. I.e. number of seconds since Jan 1, 1970. In that case you can quite easily convert it back to a proper date by: SELECT DATEADD(S, [your-value], '1970-01-01').

Testing that on your first value in the original question: SELECT DATEADD(S, 1615776024, '1970-01-01'), gives me: 2021-03-15 02:40:24.000.

This should "square" up with what you said above regarding newly created entries this month.

Upvotes: 1

Related Questions