to_char, to_date in Microsoft SQL Server

Question

I'm trying to convert the following command:

to_char(to_date('01/01/1970 00:00:00', 'MM/DD/YYYY HH24:MI:SS')+((1442998800)/( 60 * 60 * 24 )+(180/1440)),'DD/MM/YYYY HH24:MI:SS')

But with no success, any help is greatly appreciated

Many thanks

Why Oracle and Sql Server Both?? Please tag ur question correctly. And however Sql Server doesnt support to_char function. — Ankit Bajpai, Sep 23 '15 at 07:36
This works in oracle, i want to convert it so it works in sql server — Chrismage, Sep 23 '15 at 07:52
Seems like some kind of date add to me. it would be simpler to answer if you can provide input and desired output. — Zohar Peled, Sep 23 '15 at 08:00
the input is 1442998800 and the output is 23/09/2015 12:00:00 — Chrismage, Sep 23 '15 at 08:07
so this is some kind of translator between a big int and a datetime? — Zohar Peled, Sep 23 '15 at 08:08
if an answer solved your problem you should accept it so that other people will know that the problem is solved. — Zohar Peled, Sep 23 '15 at 09:18

score 2 · Accepted Answer · answered Sep 23 '15 at 08:14

2

Try this:

DECLARE @Input int = 1442998800
SELECT dateadd(second, @Input, '1970-01-01 03:00:00')

Explenation: it's clear that the input is the number of seconds since a specific date - turns out that date is January first, 1970, at 3 am.

answered Sep 23 '15 at 08:14

Zohar Peled

It works thanks very much Zohar you are a life saver :) – Chrismage Sep 23 '15 at 08:17

score 1 · Answer 2 · answered Sep 23 '15 at 08:18

1

This works for me:

SELECT FORMAT(DATEADD(SECOND,1442998800,'1970-01-01'),'dd/MM/yyyy hh:mm:ss')

In words' it adds 1442998800 seconds to 1970-01-01 and formats it the way you decsribed. Give it a try ;-)

answered Sep 23 '15 at 08:18

CeOnSql

no problem variations of how to do it are always helpful ;) thanks alot – Chrismage Sep 23 '15 at 08:22

2 Answers2