Trim Field in Query

[Gale Coleman]

Hello all,

I have an Access 2000 database table that has a field in it that holds a
timestamp. I am trying to move the data into SQL Server. I am having
problems moving over a table that has a time stamp with a time format that
reads as such: 9:34:34 AM.

Is there a way to do a query in Access to have it trim the seconds off the
time? I really don't want to delete them all by hand!!!

Thanks,

gale

 

[[MVP] S.Clark]

Try using one of the date/time conversion functions to modify the data to a
format agreeable with SQL Server.

cdate(), month(), day(), year(), etc.

 

[Gale Coleman]

Where do I find the conversion functions?

 

[Douglas J. Steele]

To remove the time component of a date/time field in a query, use the
DateValue function. To remove the date component, use the TimeValue
function.

 

[Van T. Dinh]

Are you using the Upsizing Wizard? or export to an ODBC Database?

I generally don't have any problem transferring DateTime data to SQL Server
2000 but I am not sure which process you use to "move" the data into SQL
Server.

 

[Gale Coleman]

Thanks all,

I finally got it to work. What worked for me was to go to Access, click on
the table and push export and then choose the SQL Server database to export
to and everything is fine!

Thanks again,

Gale