June 2, 2009 at 12:36 pm
Hi all
I have a table called LCC_TPROBLEM which has a column called Psolvdat containing a datestamp in EPOCH format.
I'm trying to write a query to select the total number of rows in the table with todays datestamp but I am lost because of the EPOCH format.
Something like:
SELECT * FROM LCC_TPROBLEM
WHERE Psolvdat = Getdate()
But using the EPOCH format. I've never dealt with EPOCH before.
The outcome I need is the total number of rows where the EPOCH date in Psolvdat = todays date.
Hope someone can help.
June 3, 2009 at 1:40 am
andy.woodward (6/2/2009)
Hi allI have a table called LCC_TPROBLEM which has a column called Psolvdat containing a datestamp in EPOCH format.
I'm trying to write a query to select the total number of rows in the table with todays datestamp but I am lost because of the EPOCH format.
Something like:
SELECT * FROM LCC_TPROBLEM
WHERE Psolvdat = Getdate()
But using the EPOCH format. I've never dealt with EPOCH before.
The outcome I need is the total number of rows where the EPOCH date in Psolvdat = todays date.
Hope someone can help.
There are two problems with your query. First of all "=Getdate()" will never give you all rows for todays date, but only those which have exactly the same time as well. You need to trim of the time part from the GETDATE() function. You can do this in various ways.
One option would be
SELECT CAST(FLOOR(CAST(GetDate() AS FLOAT)) AS DATETIME)
About the EPOCH format you need to know what base date is used. In most cases that's 1/1/1970.
You can then convert the format like this:
SELECT DATEADD(ss,Psolvdat, '01/01/1970') as [Psolvdat]
Hope this helps
[font="Verdana"]Markus Bohse[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply