August 3, 2009 at 3:02 am
Hi,
I'm wanting to make use of the new [Date] data type in SQL Server 2008 but I'm having a problem. I have a .txt file that contains a huge list of dates in the format DD/MM/YYYY. When I perform a basic data import into a table which contains a [col_Date] column which has a [Date] data type the data is loaded into the table in the format YYYY-MM-DD.
I could use something like:
CONVERT(VARCHAR(10),col_date, 101)
to update the data format to DD/MM/YYYY but I was wondering if "something" can be placed on the column to put the data into the correct format as it is being imported rather than having to run an additional task after the data import to correct the data format.
Thanks in advance.
www.sqlAssociates.co.uk
August 3, 2009 at 7:39 pm
Chris Kitchen (8/3/2009)
Hi,I'm wanting to make use of the new [Date] data type in SQL Server 2008 but I'm having a problem. I have a .txt file that contains a huge list of dates in the format DD/MM/YYYY. When I perform a basic data import into a table which contains a [col_Date] column which has a [Date] data type the data is loaded into the table in the format YYYY-MM-DD.
I could use something like:
CONVERT(VARCHAR(10),col_date, 101)
to update the data format to DD/MM/YYYY but I was wondering if "something" can be placed on the column to put the data into the correct format as it is being imported rather than having to run an additional task after the data import to correct the data format.
Thanks in advance.
Since the column is defined as a DATE type, the CONVERT(VARCHAR(10), col_date, 101) will simply be implicitly converted back to a DATE type for storage and when queried, you will still see the data as YYYY-MM-DD. There is nothing wrong with how the data is being displayed when you query the table using SSMS. Formatting of the data should occur in UI code, not in the backend database.
August 3, 2009 at 10:17 pm
Do you mean it displays that way when you view it in SSMS?
The date data is stored always the same way. It's an offset from a base time value, not a string. The display you see when querying depends on client settings.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply