How to define a datefield including only dd/mm/yy

[Tiger955]

Hi!

In a SQL-server table I have a datefield with datatype smalldatetime, as I only want to store dd/mm/yyyy, but no time, so it should store only 21/01/2014 00:00:00 and not 21/01/2014 11:13:43 for example.

The field gets the value from an access form with the function date() which should insert only 21/01/2014 00:00:00 but stored the value above.

Also Format(Date()),"dd/mm/yyyy") (in the access form field) does not bring the solution.

Thanks your help
Michael

 

[namliam]

SQL Server cannot "make up" the time if you are not sending it, it wont be stored....
So there must be either data comming from Access or a trigger inside the SQL Server that is causing the column to be timestamped.

 

[Rx_]

Using DSN-Less Linked tables (SQL Server Native Client)
The SQL Server has datetime2 with condensed data type datetime2(0)
It will display either dd/mm/yyyy or dd/mm/yyyy +time
Included: a date only Default Value using the Convert method

On the access side, a NOW will include the date time in a SQL Server field formatted for datetime2
While a Date will just be the date.

In a few cases, it is preferable to seperate the integer values of month, day, year into different columns then use a formula on the Access side to use them.

There is no formatting on the Access linked table def side.