MS Access or SQL

In SQL Server, we use CONVERT to trim down to the date. CAST should also work fine.

Assuming that MYDATE is a datetime, try this:

CONVERT(DATETIME,CONVERT(CHAR(11), MYDATE))

Regards,

BN

If you've already imported the date time data into the target db to a table and just want to view that data with the time part suppressed you can try the following query in SQL Server

SELECT CONVERT (date_time, ColumnName, 101) AS NewColName fromTableName

Where
ColumnName - Name of the datetime column in question
TableName - Name of the table which holds that field
NewColName - some title to display when showing the query result.

Specifying the style as 101 would display the datefield in mm/dd/yyyy format, the style number can be changed based upon your requirement.

If you want to do in MS Access you can use the FORMAT statement with either Short Date or Medium Date setting specified.

If you imported into Access set the column Data Type to Date/Time and the Format to Short Date(). Access stores Date/Time as a "double" so you may first need to convert the "mm/dd/yyyy" or "dd/mm/yyyy" text to a date? Check incoming properies to see after importing.

The fastest way is to change the field type to text and change the length to 11. This will remove everything to the right of the year. You can then change it back to date format or leave it as a test field.

This question was closed by the author