attemting to create a date range from unix-style dates

Question

attemting to create a date range from unix-style dates

rabisco

Ten Centuries

Points: 1032
More actions
September 23, 2010 at 4:22 pm

#100310

My database has dates in bigint datatype
The following query returns the month and year of the data in an acceptable format...
select month(dateadd(s,creationDate/1000,'1970-01-01')) as mnth, year(dateadd(s,creationdate/1000,'1970-01-01')) as yr from table
I now need to get return dates which are in september (9) and 2010. I have used the following....
select month(dateadd(s,creationDate/1000,'1970-01-01')) as mnth, year(dateadd(s,creationdate/1000,'1970-01-01')) as yr
(
select mnth,yr
from table
--where mnth = '9' and yr = '2010'
)
from table
but I keep getting the following error
Msg 156, Level 15, State 1, Line 37
Incorrect syntax near the keyword 'from'.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323546 More actions · Answer 1

i think it's just syntax: the query in the parenthesis needs an alias, and you select from THAT:

select month(dateadd(s,creationDate/1000,'1970-01-01')) as mnth, year(dateadd(s,creationdate/1000,'1970-01-01')) as yr

FROM

(

select mnth,yr

from table

--where mnth = '9' and yr = '2010'

) As MyAlias

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

rabisco Ten Centuries Points: 1032 More actions · Answer 2

Lowell (9/23/2010)
i think it's just syntax: the query in the parenthesis needs an alias, and you select from THAT:
select month(dateadd(s,creationDate/1000,'1970-01-01')) as mnth, year(dateadd(s,creationdate/1000,'1970-01-01')) as yr
FROM
(
select mnth,yr
from table
--where mnth = '9' and yr = '2010'
) As MyAlias

Thanks.