Skip to main content

Return to Question

deleted 22 characters in body; edited title
Source Link
Dale K
Dale K
  • 26.9k
  • 15
  • 50
  • 81

sql server SQL Server datetime to bigint (epoch) overflow

I have a 'datetime' column with value 2013-03-22 15:19:02.000

I need to convert this value into epoch time and store it in a 'bigint' field

The actual epoch value for the above time is,

1363945741898

1363945741898, when I use

  select DATEDIFF(s, '1970-01-01 00:00:00', '2013-03-22 15:19:02.000')

I get,

1363965542

1363965542, when I use

select DATEDIFF(ms, '1970-01-01 00:00:00', '2013-03-22 15:19:02.000')

I get,

Msg 535, Level 16, State 0, Line 1 The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

Msg 535, Level 16, State 0, Line 1 The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

How to get the exact epoch value from the 'datetime' field

I use sql serverSQL Server 2008. Also this should work with 2005.

Thanks.

sql server datetime to bigint (epoch) overflow

I have a 'datetime' column with value 2013-03-22 15:19:02.000

I need to convert this value into epoch time and store it in a 'bigint' field

The actual epoch value for the above time is,

1363945741898

when I use

  select DATEDIFF(s, '1970-01-01 00:00:00', '2013-03-22 15:19:02.000')

I get,

1363965542

when I use

select DATEDIFF(ms, '1970-01-01 00:00:00', '2013-03-22 15:19:02.000')

I get,

Msg 535, Level 16, State 0, Line 1 The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

How to get the exact epoch value from the 'datetime' field

I use sql server 2008. Also this should work with 2005.

Thanks.

SQL Server datetime to bigint (epoch) overflow

I have a 'datetime' column with value 2013-03-22 15:19:02.000

I need to convert this value into epoch time and store it in a 'bigint' field

The actual epoch value for the above time is, 1363945741898, when I use

  select DATEDIFF(s, '1970-01-01 00:00:00', '2013-03-22 15:19:02.000')

I get, 1363965542, when I use

select DATEDIFF(ms, '1970-01-01 00:00:00', '2013-03-22 15:19:02.000')

I get,

Msg 535, Level 16, State 0, Line 1 The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

How to get the exact epoch value from the 'datetime' field

I use SQL Server 2008. Also this should work with 2005.

Source Link
itsraja
itsraja
  • 1.7k
  • 5
  • 33
  • 52

sql server datetime to bigint (epoch) overflow

I have a 'datetime' column with value 2013-03-22 15:19:02.000

I need to convert this value into epoch time and store it in a 'bigint' field

The actual epoch value for the above time is,

1363945741898

when I use

  select DATEDIFF(s, '1970-01-01 00:00:00', '2013-03-22 15:19:02.000')

I get,

1363965542

when I use

select DATEDIFF(ms, '1970-01-01 00:00:00', '2013-03-22 15:19:02.000')

I get,

Msg 535, Level 16, State 0, Line 1 The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

How to get the exact epoch value from the 'datetime' field

I use sql server 2008. Also this should work with 2005.

Thanks.