Date Time format for MS Access and Criteria 1

[nrugado]

Hello, I am migrating an ms access db to SQL server. The fields that automatically stored the date in access have been changed to smalldatetime or datetime format. I have several instances where I am using a list box to select the date for criteria in a query or using a manual data entry for cirteria. However, it does not seem to recognize the date part when there is a time associated with the field.

i.e. 1/1/02 is the date that I would like to pull data for.

Before I could either enter 1/1/02 in a dialog box or refer the form to a list box that had this value. This was the value that was stored in the table.
When I changed the field type it now stores 1/1/02 1:40:00PM in the field. The query is having problems pulling out the date as a date only without the time. There are no results being pulled even though there are several records for that "Date".

Does anyone have any suggestions on how to approach this issue?

 

[Esoteric]

Ah,

Convert(char(10), DateFieldName, 100)

Will provide you the simple date format of 04/02/2003.

Example if your datefieldname is INVDATE use this:
Convert(char(10), INVDATE, 100) AS INVDATE

Use it wherever you want to utilize that format, SELECT
WHERE, ORDER BY, INNER JOIN, etc.