May 9, 2011 at 8:41 pm
Hi,
I was hoping I could get a answer to my stupid question. I have this query:
SELECT DISTINCT
TOP 100 CONVERT(DATETIME, CONVERT(VARCHAR, CONVERT(DATETIME, DATEADD(S, seconds_since_epoch, '19700101'), 101), 101))
AS EXPR1
FROM dbo.scr_fct_exact_access scr_fct_exact_access
ORDER BY CONVERT(DATETIME, CONVERT(VARCHAR, CONVERT(DATETIME, DATEADD(S, seconds_since_epoch, '19700101'), 101), 101)) DESC
Basically it take a date that is in the take as epochtime and converts it to standard and shows all the distinct results. Works great. THe problem creeps in when I add a WHERE statement. I did this:
SELECT DISTINCT
TOP 100 CONVERT(DATETIME, CONVERT(VARCHAR, CONVERT(DATETIME, DATEADD(S, seconds_since_epoch, '19700101'), 101), 101))
AS EXPR1
FROM dbo.scr_fct_exact_access scr_fct_exact_access
where EXPR1 > '2011-05-07 00:00:00.000'
ORDER BY CONVERT(DATETIME, CONVERT(VARCHAR, CONVERT(DATETIME, DATEADD(S, seconds_since_epoch, '19700101'), 101), 101)) DESC
THis idea is the WHERE would look at the result of the select (EXPR1) and only show results > the specificed date. But this gives me invalid colum error in query analyser. To get it to work I have to do it like this using the converstion in the WHERE statement:
SELECT DISTINCT
TOP 100 CONVERT(DATETIME, CONVERT(VARCHAR, CONVERT(DATETIME, DATEADD(S, seconds_since_epoch, '19700101'), 101), 101))
AS EXPR1
FROM dbo.scr_fct_exact_access scr_fct_exact_access
where (Select distinct
TOP 100 CONVERT(DATETIME, CONVERT(VARCHAR, CONVERT(DATETIME, DATEADD(S, seconds_since_epoch, '19700101'), 101), 101))) > '2011-05-07 00:00:00.000'
ORDER BY CONVERT(DATETIME, CONVERT(VARCHAR, CONVERT(DATETIME, DATEADD(S, seconds_since_epoch, '19700101'), 101), 101)) DESC
Wondering if there is a way I can reference the result of the select query rather than having to specify the converstion again?
May 10, 2011 at 12:53 am
How about:
SELECT ....
FROM dbo.scr_fct_exact_access scr_fct_exact_access
where seconds_since_epoch < datediff(ss, '1970-01-01', '2011-05-07')
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply