converting UNIX timestamp problems. Help!!!

• tschuler-738392

  Ten Centuries

  Points: 1172

  More actions

  November 12, 2013 at 9:32 am

  #151294

  I am trying to run a query against a table with unix date time stamp information. The field is an int field. The raw data in that filed looks like the following.

  1384217102

  1384216278

  1384193474

  1384191885

  1384190277

  1384185678

  1383950374

  1383950374

  1383950340

  My Query is as follows.

  select TransID, DATEADD(second, dateTimeOrigination, '1970-01-01 00:00:00'), *

  from ciscorawdata

  where 1=1

  and DATEADD(second, dateTimeOrigination, '1970-01-01 00:00:00') > '2012-08-01'

  and DATEADD(second, dateTimeOrigination, '1970-01-01 00:00:00') < '2012-09-01'

  I keep getting the following error when I run the query.

  Msg 102, Level 15, State 1, Line 1

  Incorrect syntax near ' '.

  Can anyone help me in determining what is causing this?

• Sean Lange

  SSC Guru

  Points: 286589

  More actions

  November 12, 2013 at 9:42 am

  #1665783

  You are going to have to provide more details here. I took your sample data and turned into something consumable but there are no errors.

  create table ciscorawdata

  (

  TransID int identity,

  dateTimeOrigination int

  )

  insert ciscorawdata

  select 1384217102 union all

  select 1384216278 union all

  select 1384193474 union all

  select 1384191885 union all

  select 1384190277 union all

  select 1384185678 union all

  select 1383950374 union all

  select 1383950374 union all

  select 1383950340

  select TransID, DATEADD(second, dateTimeOrigination, '1970-01-01 00:00:00'), *

  from ciscorawdata

  where 1=1

  and DATEADD(second, dateTimeOrigination, '1970-01-01 00:00:00') > '2012-08-01'

  and DATEADD(second, dateTimeOrigination, '1970-01-01 00:00:00') < '2012-09-01'

  I have a feeling because you have 1=1 first, there is some dynamic string building going on here somewhere???

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/