DATETIME compare issue

I am trying to do a select where one set of date/time columns are greater than another. My date is stored in one column while the time is stored in another. Only the date portion of the date column is valid and only the time portion of the time column is valid.
Example my date column value is 8/1/2007 01:01:01 AM and my time column value is 1/1/2007 07:23:49 AM which in my system means the last update time was 8/1/2007 07:23:49 AM.

My select statment looks like this.

SELECT *
FROM CHANGE
WHERE
CONVERT(DATETIME( DATE_LAST_ALTERED, TIME_LAST_ALTERED)) < CONVERT(DATETIME(DATE_APPROVED,TIME_APPROVED))

I get an incorrect syntax near 'DATE_LAST_ALTERED'.

Am I totaly missing the point of DATETIME?

Matt

You need to give a look to the CAST AND CONVERT article in books online; your syntax for your CONVERT function is not correct. And yes, you might very well be missing the point of date and time in SQL Server. You should normally store date and time in a single column. And your usage here surely indicates that your date and time should be stored in a single column. You might be able to run with a where clause something like:

Code Snippet

where cast(floor(cast(date_last_altered as float)) as datetime)
+ time_last_altered
- floor(cast(time_last_altered) as float))
< cast(floor(cast(date_approved as float)) as datetime)
+ time_approved
- floor(cast(time_approved) as float))

Will someone please check me please?

|||Kent,

Thanks for the quick response. I was going to put a comment in about the fact that the 2 columns to store the data was not my doing and that I am stuck with it; knowing I would get that comment in response.

Apparently in DB2 DATETIME(col1,col2) is supported so this has never been an issue. I am also stuck with constraints on the length of my WHERE statement. (Imposed by the application that is taking the WHERE statement and storing it.)

Looks like I wil have to figure another way.

Thanks again.
Matt
|||

Very well; is this then a DB2 question and not a SQL Server question?

|||Can you modify/add a column to the database then? I would suggest u make a varchar column and combine the 2 columns, otherwise u may have to do a lot of number crunching due to ur where clause constraints - what is the exact contraint?