Where can DATEFORMAT and CONVERT be used? (sql server)

Question

Hi, I am running SQL Server 2005 (express). I am trying to use DATEFORMAT and CONVERT in my queries. I am running into problems, so I first wanted to ask if those functions can be used everywhere.

I already know they can be used in SELECT. But can they be used in WHERE as well?

For example:

SELECT * 
    FROM review AS R, section AS SC, class AS CL, inserted AS M, section AS SC2, class AS CL2 
    WHERE 
    R.sectionId=SC.sectionId AND SC.classId=CL.classId AND
    M.sectionId=SC2.sectionId AND SC2.classId=CL.classId AND
    CL.classYear=CL2.classYear AND CL.classQuarter=CL2.classQuarter AND
    M.meetingDay = DATEPART(weekday,R.reviewStart) AND
    (
     CONVERT(VARCHAR(8), R.reviewStart, 108) between CONVERT(VARCHAR(8), M.meetingStart, 108) and CONVERT(VARCHAR(8), M.meetingEnd, 108) OR
     CONVERT(VARCHAR(8), R.reviewEnd, 108) between CONVERT(VARCHAR(8), M.meetingStart, 108) and CONVERT(VARCHAR(8), M.meetingEnd, 108) OR
     CONVERT(VARCHAR(8), M.meetingStart, 108) between CONVERT(VARCHAR(8), R.reviewStart, 108) and CONVERT(VARCHAR(8), R.reviewEnd, 108) OR
     CONVERT(VARCHAR(8), M.meetingEnd, 108) between CONVERT(VARCHAR(8), R.reviewStart, 108) and CONVERT(VARCHAR(8), R.reviewEnd, 108)
    )

See the use in M.meetingDay = DATEPART ... As well as in all parts of the BETWEEN thing.

Is that legal?

Thanks

Answer 1

Yes it is legal

You don't say what the problem you are experiencing is so I can't offer much guidance but I would be inclined to take a look at the length of your varchar string. A datetime string requires more than 8 characters; are you truncating part of the string by the use of varchar(8).

Answer 2

Maybe is a bit off topic but you should investigate moving to SQL 2008 Express and use the new datetime types introduced with 2008, since there is the new time type that stores exactly the time of the day (w/o the date part).