Date Time Format

[wfairbanks]

I am having a completely opposite problem of the other date time format thread. When importing my tables from access into SQL it gives me an import error. Well I figured out that it was the date but only in certain fields. Some tables have a date field 3 instances (for example start date, end date, date). One of the date fields is preventing the table from importing. It is defaulting to smalldatetime. I have to change it to text for it to import. Then the date has been changed from 01/01/03 to 2003-01-01 00:00:00. How can I prevent this from happening? I have to change it on several tables just so they will import.

 

[SQLBill]

What don't you want to happen?

DATETIME and SMALLDATETIME store the date AND time. If one is left off it creates a default. Also, it's stored as an eight-byte field. First four bytes represent the number of days after the default date and the second four bytes represent the number of milliseconds after midnight.

The format is only created by the front-end. So, you set your query to report the date/time in whatever format you desire.

-SQLBill

 

[wfairbanks]

I don't want the time to appear. I want the date to transfer just as it is 01/01/03. Sorry for not making myself clearer.

 

[ecobb]

As SQLBill said, SQL Server is going to list the date AND time. If a time isn't specified, it defaults to 00:00:00. Likewise, if a date wasn't specified, it would default to 1/1/1900. This is how SQL Server stores Date/Time values. When you display the date/time in your front-end application you can format it however you want it to look, but the information in the database will have to be stored as: 2003-01-01 00:00:00

 

[SQLBill]

Again, you CANNOT save JUST the date in SQL Server. You CANNOT save the datetime in a specific format.

Quote from Microsoft's INSIDE SQL SERVER 2000 Chapter 6, Date and Time Datatypes:

Internally, datetime and smalldatetime values are stored completely differntly from how you enter them or how they are displayed. They are stored as two separate components, a date component and a time component. The date is stored as the number of days before or after the base date of January 1, 1900. For datetime values, the time is stored as the number of clock ticks after midnight, with each tick representing 3.33 milliseconds, or 1/300 of a second. For smalldatetime values, the time is stored as the number of minutes after midnight. You can actually see these two parts if you convert a datetime value to a binary string of 8 hexadecimal bytes.

Note: above italics are from the original, any typos are mine.

That said, if you want to display the date/time in a specific format, you need to have your front-end application (VB, Access, Query Analyzer, etc) do the conversion. And no matter what, SQL Server WILL store BOTH the DATE and TIME. It will assign a default if one isn't provided.

Defaults: Date = January 1, 1900
Time = 00:00:00.000 (midnight)

-SQLBill

 

[wfairbanks]

I was able to import the date without the time. I chose nvarchar for data type.

 

[SQLBill]

Just be aware that you won't be able to do datetime computations (ie. DATEDIFF, DATEADD, etc) with dates as anything but DATETIME or SMALLDATETIME.

-SQLBill

 

[wfairbanks]

Thanks SQLBill
I may end up changing things back but I am going to try this for now.