SQL to query columns that are in seconds since 1/1/1970

[Jim in Arizona]

I've got an access database I'm connecting to using ASP.NET. Several columns
in the various tables of this DB are in a time format of seconds since
12:00:00 1/1/1970. Is there a way to run a query using SQL that I will be
able to make sense of. For instance:

SELECT Column1, Column2 FROM TableA
WHERE Column3 >= #3/30/08# AND Column3 <= #4/3/08#

I naturally don't know what these date ranges would be in seconds since
midnight on Jan 1st, 1970. Is there a method in access SQL that would allow
me do this SQL? Like:

SELECT Column1, Column2 FROM TableA
WHERE #1/1/1970#.AddSeconds(Column3) >= #3/30/08#. AND
#1/1/1970#.AddSeconds(Column3) <= #4/3/08#

I don't know if access SQL supports such a method (I'm guessing it does
not!). The method that I use in the example above is taken from .NET. Is
there something equivalent in access?

TIA,
Jim

 

[Klatuu]

In a Jet (Access) database, all times in a date time field are carried as
12:00:00 AM, unless they were inserted into the field using a time component.
Then it would have a time. So, if no times are in the field, you don't have
to be concerned about the time part.

 

[Jim in Arizona]

Thanks Dave, but I still don't know how to query the fields then. They are
in seconds since Jan 1, 1970. So, I can't use a query like:

select * from table where columnA = #1/12/07#

because the field is not a date but a very long integer; An actual number:
1206396000, which, when I use .NET for conversion, is 3/24/2008 10:00:00 PM.
The actual type of these columns is Number, not Date/Time. Somehow, I need
to convert these Numbers into the date/time that they are within the SQL
that I send to the access database.

 

[Klatuu]

I guess I am at a loss, Jim. I think I remember somewhere seeing a way to
turn a number like that into a date, but I can't remember how do it or where
I saw it.

 

[Jim in Arizona]

Well, thanks anyway Dave.

Perhaps I'll ask the same question over at the asp.db group and see what
they have to say over there. Someone over there may have run into the same
thing before. I may get lucky. If I find an answer, I'll repost it here.

Jim

 

[John Spencer]

I know how to do this using a VBA function.

DateAdd("s",ColumnA,#1/1/1970#)

I have no idea of how to handle this using ASP.Net.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Jim in Arizona]

I know how to do this using a VBA function.

DateAdd("s",ColumnA,#1/1/1970#)

I have no idea of how to handle this using ASP.Net.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

That's smiilar to what I've decided to do.

In VB.NET

Dim Secs As Integer = DateDiff(DateInterval.Second, #1/1/1970#, #4/4/2008
3:30:00 PM#)

Then, the SQL I throw at the access database:

"SELECT Column1, Column2 FROM Table A " & _
"WHERE StartDate = " & Secs & ";"

This will work fine although I was hoping for a way to do it in Access SQL.
No big deal.

The Access database that I'm querying against came installed with our PBX
software so I hesitate to make any modifcations to it. Using the VB.NET
example above in my ASP.NET pages, I don't have to do any modifications to
the DB itself; just connect and send my SQL.

Thanks John.