Time and date specs in SQL

[Guest]

Using Office 2003 and Windows XP:

The following SQL will create a table with two dates and two times columns,
but how can I specify that the dates are to be MediumDate and the times are
MediumTime in SQL?

CREATE TABLE [tblUserTracking1] (USERNAME VARCHAR NULL,
BOXNAME VARCHAR NULL, DATEIN DATE NULL, TIMEIN DATE NULL,
DATEOUT DATE NULL, TIMEOUT DATE NULL);
CurrentProject.Connection.Execute sSQL

Thanks in advance.

 

[Marshall Barton]

Using Office 2003 and Windows XP:

The following SQL will create a table with two dates and two times columns,
but how can I specify that the dates are to be MediumDate and the times are
MediumTime in SQL?

CREATE TABLE [tblUserTracking1] (USERNAME VARCHAR NULL,
BOXNAME VARCHAR NULL, DATEIN DATE NULL, TIMEIN DATE NULL,
DATEOUT DATE NULL, TIMEOUT DATE NULL);
CurrentProject.Connection.Execute sSQL

Your thinking is flawed, making your objectve near
meaningless.

First a Date type field contains both a date part and a time
part so there is no need for separate fields for date and
time. Doing so, wastes space, make queries run slower and
can make using the fields in calculations very difficult.

Second, Medium Date and Medium Time are formats that only
affect how a value is displayed, formating has no effect on
the value of a field. Furthermore, you should never display
a table's sheet view to users, so the format of a field is
irellevant.