Convert to Datetime

[AccessHunter]

Hi,

I am trying to input data to a SQL server table from my source Access 2003 table. The table name is Cases. One of the columns 'DateClosed' has datetime as data type in the source database. In the input SQL I was trying to convert 'DateClosed' as follows,

CREATE TABLE [Term2000SQL].[dbo].[Cases] (
[AutoKey] int NOT NULL,
[DocketNumber] nvarchar(9),
[Aliases] nvarchar(255),
[DateReviewed] datetime,
[DateReceived] datetime,
[DateFiled] datetime,
Convert (datetime,[DateClosed],mm/dd/yyyy),
[BoxNumber] nvarchar(10),

But it keep giving me the message saying, Incorrect syntax near the keyword 'Convert'

Please help,

Thanks

[Shashi Sadasivan]

You are only creating a table. at the time of creation you specify the field name and the datatype.
obviously SQL does not like any other things to be put in.

Once you create the table

CREATE TABLE [Term2000SQL].[dbo].[Cases] (
[AutoKey] int NOT NULL,
[DocketNumber] nvarchar(9),
[Aliases] nvarchar(255),
[DateReviewed] datetime,
[DateReceived] datetime,
[DateFiled] datetime,
[DateClosed] datetime,
[BoxNumber] nvarchar(10), ....

then while tranferring data u can convert that field to the datetime value.

if the table is already created then you may want to alter the table to change the atatype of the column [DateClosed]

[AccessHunter]

You are only creating a table. at the time of creation you specify the field name and the datatype.
obviously SQL does not like any other things to be put in.

Once you create the table

CREATE TABLE [Term2000SQL].[dbo].[Cases] (
[AutoKey] int NOT NULL,
[DocketNumber] nvarchar(9),
[Aliases] nvarchar(255),
[DateReviewed] datetime,
[DateReceived] datetime,
[DateFiled] datetime,
[DateClosed] datetime,
[BoxNumber] nvarchar(10), ....

then while tranferring data u can convert that field to the datetime value.

if the table is already created then you may want to alter the table to change the atatype of the column [DateClosed]

If the table is already created on the SQL server database, the Input Data wizard will not let you modify the SQL !! Could you please explain your answer little more on how else to do the convert on transferring data?

Thanks

[ck9663]

If the table is already created on the SQL server database, the Input Data wizard will not let you modify the SQL !! Could you please explain your answer little more on how else to do the convert on transferring data?

Thanks

import the data into a non-existing table (or delete the existing table first). it will be uploaded with the same structure as your table in access...

then you can:
change the structure..

or

create a table and do an INSERT INTO with all the necessary conversion

or

do a SELECT cast(oldfield as newtype) as newfield into newtalbe from oldtable
drop the oldtable
sp_rename newtable, whatireallywantmynewtablenametobe

-- CK