searching between dates with "where" in msSQL

ranger wrote: ... i am using "smalldatetime" in msSQL2005 and also would like to create a mask so that only date: 1/1/2012 would be saved. ... same for time: 1:30AM in a seperate field. msSQL is saving now as 1/1/2012 01:30:00AM or some such...

The Smalldatetime type is of the form YYYY-MM-DD hh:mm:ss. If you wish to save just the date, then use the Date type. That is of the form YYYY-MM-DD. The Time type is of the form hh:mm:ss.

Where necessary, use SQL Server's datetime functions like convert() to get the right format. For example, suppose you had saved the date in the columns dateinx and dateoutx as Date type YYYY-MM-DD. Then you could do something like this

SELECT *

FROM schedule

WHERE dateinx <= convert(Date, getdate(), 101) AND dateoutx >= convert(Date, getdate(), 101)

If you want to separate the date and time components you have to upgrade to a later version of mssql. 

To do your search, use this logic:

and yourdatefield >= date1

and yourdatefield < the day after date 2.

Don't worry about date formats.  Dates are actually numbers.  There are functions both in sql server and coldfusion that enable you to format these.