epochtime to datetime?? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

epochtime to datetime??

Author

Topic

kbdrand
Starting Member

14 Posts

Posted - 2002-07-10 : 13:37:22

I'm trying to convert or cast a date represented as a number to a Datetime. The date field is a field called 'Create_Date' and it seems to be stored as epoch time in a number field.

In my select statement I have tried using :

CAST(CHG1.Create_Date AS datetime) AS createdate

as well as :

CONVERT(datetime, CHG1.Create_Date) AS createdate

Neither of these seem to work. I get a Syntax Error with Cast saying that I am missing an operator, and I get undefined function with Convert. I am using MS SQL 7.0 and I'm using Access 2002 to perform the query.

What am I doing wrong? And how should I be converting these dates?

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-07-10 : 13:56:44

quote:

it seems to be stored as epoch time in a number field.

You'll definitely need to provide a definition of 'epoch time' and examples of how to convert it to a standard date and time.

Jonathan Boott, MCDBA

kbdrand
Starting Member

14 Posts

Posted - 2002-07-10 : 14:00:16

epochtime = Unix number time format in seconds since the year 1970 (Jan 1 1970 I believe). I'm guessing that this is the format that the date is in since it's stored in a number field and the numerical representation seems to be in that format.

I need to be able to run a report that says start at a particular Date (June 1 2002 for instance) and end at another date (June 30 2002 for instance). I only want the records that fall within those dates.

So how do I switch the 'epochtime' to a MS SQL datetime format?

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-07-10 : 14:02:39

quote:

epochtime = Unix number time format in seconds since the year 1970 (Jan 1 1970 I believe). I'm guessing that this is the format that the date is in since it's stored in a number field and the numerical representation seems to be in that format.

So how do I switch the 'epochtime' to a MS SQL datetime format?

You would use the dateadd() function in t-sql, e.g. dateadd( s, '1970-01-01', @epochtime )

Jonathan Boott, MCDBA

kbdrand
Starting Member

14 Posts

Posted - 2002-07-10 : 14:16:10

I guess I just won't be able to do this in MS Access. I'm trying to use Access to do the queries and reports without having to write a little application. Unfortunately dateadd() doesn't seem to work either. When I use dateadd() with the first parameter as 's' or 'second' it pops up a box asking for the value of 's' or 'second'.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-10 : 14:39:37

The MS Access version of DateAdd requires the s parameter to be enclosed in quotation marks:

SELECT DateAdd("s", dateValue)

...while SQL Server does not use the quotes.

kbdrand
Starting Member

14 Posts

Posted - 2002-07-10 : 14:53:01

Thanks for the info. But now I'm still getting an error. Here's the two ways I've tried it:

dateadd("ms",CHG1.Create_Date,"1970-01-01")

or

dateadd("ms","1970-01-01",CHG1.Create_Date)

The first gives me an invalid procedure call, and the second gives me a data type mismatch, even though the CHG1.Create_Date field is a number and the column is set as numeric (The actual data type is listed as 'Long Integer').

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-07-10 : 14:56:01

First, use "s" for seconds rather than "ms" for milliseconds.

Second, it's the second form that's correct.

Third, you might need to use the Access-specific character literal to indicate a literal date. I think "#1970-01-01#" might be right.

Jonathan Boott, MCDBA

kbdrand
Starting Member

14 Posts

Posted - 2002-07-10 : 15:56:45

Hmm, I tried all of your suggestions but I'm still getting a type mismatch. Here's a sample of the Create_Date field:

937248737
937249382
937860084

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-10 : 16:38:23

I got this to work:

SELECT DateAdd("s", 100, #1/1/1970#)

Access' DateAdd function does not recognize milliseconds, so if you have a millisecond value, you need to do this:

SELECT DateAdd("s", msValue/1000 ,#1/1/1970#)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources