Date Query question

I am trying to access one of our SQL student tables by the change_date field. What is the proper way to get results when looking for a change_date >= "9/25/2006".

The field data looks like this: 9/25/2006 8:15:02 AM

Thx

The answer depends on how you are accessing the database. What application and/or API are you using? And what difficulties are you encountering?|||

I'm using the the query option in SQL Server Enterprise Manager. I'm getting no results when I know the data is there.

Thx

|||

Your change_date is most likely a Date/Time data type. In which case your sql string will look like this:

change_date >= #9/25/2006#

Whenever your querying data from a table, you need to think about what type of data of the fields you want to filter you search on.

If it's a date, time, or both, your gonna wrap your data within numeric/lbs. signs (Shift-3).

ex: date >= #1/1/2000#

If it's a string, your gonna wrap your data in single quotes.

ex: string_value = 'my value'

If it's a integer, you will not need to wrap the data in anything.

ex: integer_value = 100

|||

In order to retreive data from a table, where the data contains a time component other than midnight, you will have to accomodate the time issue in your search criteria.

One of the best methods is the following:

WHERE ( Change_Date >= '2006/09/25'
AND Change_Date < '2006/09/26'

This 'brackets' the day from midnight the 25th until just before midnight the 26th. Therefore the time of day is immaterial -all times during the day will be retreived. And it will use any indexing that may be available for Change_Date.

|||

AJ,

You forgot to remove your Access Developer brain and insert the SQL Server brain this morning. (Long holidays do that to us. )

T-SQL requires single quotes around the datetime values, whereas Access uses the pound sign (#).

|||

Arnie,

Works great, thanks.