Epoch Date

Help!!! I have an application I support that the vendor is not being
cooperative sin giving me data purge routines. The application uses Epoch
dates (# of ms since 1/1/1970). I found this script on Oracle but need help
converting it to T-SQL where it works without the arithmetic overflow error:
SELECT to_char(
TO_DATE('01-JAN-1970','DD-MON-YYYY') + &epoch_num/86400
,'dd-mon-yyyy hh24:mi:ss') normal_date
FROM dual;Try,
select dateadd(day, epoch_num / 86400, cast('19700101' as datetime))
AMB
"Cathy Soloway" wrote:

> Help!!! I have an application I support that the vendor is not being
> cooperative sin giving me data purge routines. The application uses Epoch
> dates (# of ms since 1/1/1970). I found this script on Oracle but need he
lp
> converting it to T-SQL where it works without the arithmetic overflow erro
r:
> SELECT to_char(
> TO_DATE('01-JAN-1970','DD-MON-YYYY') + &epoch_num/86400
> ,'dd-mon-yyyy hh24:mi:ss') normal_date
> FROM dual;
>|||Hi
here is the solution:
select DATEADD (ms,epoch_num,'01-JAN-1970') normal_date
from <TABLE>
alternatively just go through DATEDIFF and DATEADD functions available in BO
L
thanks and regards
Chandra
"Cathy Soloway" wrote:

> Help!!! I have an application I support that the vendor is not being
> cooperative sin giving me data purge routines. The application uses Epoch
> dates (# of ms since 1/1/1970). I found this script on Oracle but need he
lp
> converting it to T-SQL where it works without the arithmetic overflow erro
r:
> SELECT to_char(
> TO_DATE('01-JAN-1970','DD-MON-YYYY') + &epoch_num/86400
> ,'dd-mon-yyyy hh24:mi:ss') normal_date
> FROM dual;
>|||How do I get around the arithmetic overflow this statement gives me?
"Chandra" wrote:
> Hi
> here is the solution:
> select DATEADD (ms,epoch_num,'01-JAN-1970') normal_date
> from <TABLE>
>
> alternatively just go through DATEDIFF and DATEADD functions available in
BOL
> thanks and regards
> Chandra
>
> "Cathy Soloway" wrote:
>|||Hi
Please go through the following post. This might help you
http://msdn.microsoft.com/library/d...br />
2f3o.asp
thanks and regards
Chandra
"Cathy Soloway" wrote:
> How do I get around the arithmetic overflow this statement gives me?
> "Chandra" wrote:
>|||Are you sure it is milliseconds?
If you see the formaula (epoch_num / 86400), 86400 is the number of seconds
in a day, so the formaula seems to be calculating days. Try second instead
milli:
select DATEADD (second, epoch_num, '01-JAN-1970') normal_date
from <TABLE>
AMB
"Cathy Soloway" wrote:
> How do I get around the arithmetic overflow this statement gives me?
> "Chandra" wrote:
>|||Cathy,
Here's one way. If your epoch_num value is a decimal type,
you'll need to convert it to bigint, so you can use the modulo function.
declare @.epoch_num bigint
set @.epoch_num = 1092347839827
select @.epoch_num/86400000 + DATEADD (ms,@.epoch_num%86400000,'19700101')
Steve Kass
Drew University
Cathy Soloway wrote:
>How do I get around the arithmetic overflow this statement gives me?
>"Chandra" wrote:
>
>|||Thanks Steve. This does it.
"Steve Kass" wrote:

> Cathy,
> Here's one way. If your epoch_num value is a decimal type,
> you'll need to convert it to bigint, so you can use the modulo function.
> declare @.epoch_num bigint
> set @.epoch_num = 1092347839827
> select @.epoch_num/86400000 + DATEADD (ms,@.epoch_num%86400000,'19700101')
> Steve Kass
> Drew University
> Cathy Soloway wrote:
>
>