Hi Everyone
For some reason, all the values I imported into SS got changed to text even though the table definitions clearly state that a date is a date and a number is a number. I checked the value in Excel and it is showing as correct format. The date "Nov 24,2021" shows up as that in Excel. However, in SS it is showing up as 2024-Nov-21. I don't know what happened in the import process. I imported the data by going to the DB and then selecting Tasks -> Import Data. In addition to the date, there is a problem with numbers too. They are being treated as string even though the table field is defined as numeric(18,5).
How can I fix this?
[Source Information]
Table: C:\Users\.csv
Column: QUOTE_DATE
Column Type: string [DT_STR]
SSIS Type: string [DT_STR]
Mapping file (to SSIS type):
[Destination Information]
Destination Location :
Destination Provider : SQLNCLI11
Table: [dbo].[Data]
Column: QUOTE_DATE
Column Type: date
SSIS Type: database date [DT_DBDATE]
Mapping file (to SSIS type): C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\CommonExtensions\Microsoft\SSIS\150\MappingFiles\MSSQLToSSIS10.XML
[Conversion Steps]
Step 1: DT_STR to DT_DBDATE
SSIS conversion file: C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\CommonExtensions\Microsoft\SSIS\150\binn\DtwTypeConversion.xml
Thank you
In SQL the only format allowed for dates is based on ISO 8601. It is"yyyy-mm-dd" , however various products allow you to do proprietary local dialects if you don't give a damn about standards and portability. A date should never have alpha month names or abbreviations in it. That's just bad programming. You want to see how confusing it can get, Google the names of months in Czech and in Slovak; these two language groups used to be in the same country and their month names look nothing alike! I do not know Excel, but is there some way that you can force your spreadsheet to conform to international standards?
Please post DDL and follow ANSI/ISO standards when asking for help.
December 4, 2021 at 5:51 pm
it worked. thank you!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply