Short date for SQL Server date column

[bayman]

With an Access 97 front-end, and a SQL Server 2000 back-end, I have a field on a form representing a SQL datetime column that I only want to see in a Short Date format (xx/xx/xxxx). I set the format accordingly (Short Date), and the field displays as expected. However, when I attempt to edit the field, the 'time' portion of the field appears. Is there any way to prevent this?

Thanks in advance.

 

[Pat Hartman]

Date/Time fields ALWAYS store date and time. The format is simply how you have chosen to display the data. If the field is showing time when you select it, you must be storing a time value. Are you using Now() to populate the field?

 

[bayman]

Date/Time fields ALWAYS store date and time. The format is simply how you have chosen to display the data. If the field is showing time when you select it, you must be storing a time value. Are you using Now() to populate the field?

I know the date and time are stored, but I don't want to reveal the time portion. Using the 'short date' display format, that's accomplished. However, when clicking on the text box to edit the data, the entire date and time appear. I want to prevent this. Any hope?

 

[Pat Hartman]

No, If the field contains a time element, it will show at this point. Why are you storing time when you don't want to show it?

 

[bayman]

The backend DB is SQL Server. If you can store only the date, without the time, in a SQL column with a datetime or smalldatetime data type, I'm not aware of how.

 

[Pat Hartman]

I don't seem to be making myself clear. This behaviour occurs when there an actual time value stored along with the date. If you never put a time value in the field, it will not show the time part of the field when it is formatted as short date. If the field does contain a time value, the time value will show when you click your cursor in the field despite the short time format. Is that clear? If you do not want time to be stored in this field, don't put it there. You may be mistakenly using Now() rather than Date(). Now() enters the current time as well as the current date whereas, Date() enters ONLY the current date.

 

[bayman]

I don't seem to be making myself clear. This behaviour occurs when there an actual time value stored along with the date. If you never put a time value in the field, it will not show the time part of the field when it is formatted as short date. If the field does contain a time value, the time value will show when you click your cursor in the field despite the short time format. Is that clear? If you do not want time to be stored in this field, don't put it there. You may be mistakenly using Now() rather than Date(). Now() enters the current time as well as the current date whereas, Date() enters ONLY the current date.

The field in question is populated by SQLServer upon row creation. The default value is SQL's getdate() function, which includes the time.