CODEWITHSUNDEEP
sql-serversql-server-2008ssms
CIPHER
CIPHER

Reputation: 237

Importing date column ends in error

While i try to upload a flatfile which contains date fields with data type DATE in ssms import/export wizard throws an error like below

Conversion failed when converting date and/or time from character string

Actual data in flat file

  Period
12/17/2003
01/01/0001
10/25/2001
01/01/0001

Please help me how to upload those dates ..... i know error which lies in fileds which contains '01/01/0001' but it is business logic i cant able to change those data.... !!!

Thanks in advance

Upvotes: 0

Views: 955

Answers (3)

Veera
Veera

Reputation: 3492

In your case, One possibility to convert the Date in integer(37972) to DATETIME after the insertion is by using dateadd.

Run the below query, after you Insert the values into VARCHAR type column.

 UPDATE YourTableName 
 SET ColumnWithVarcharType = CAST(DATEADD(d, 
      CAST(ColumnWithVarcharType AS INT), -2) AS VARCHAR(20))
 WHERE IsNumeric(ColumnWithVarcharType) = 1

Above query will update only the selected values having integer(37972) value. It won't care about values like '01/01/0001'.

SELECT DATEADD(d, 0, 0)   => 1/1/1900
SELECT DATEADD(d, 37972, -2)  => 12/17/2003

by default the DB return the initial date used in the sql server for the query SELECT DATEADD(d, 0, 0). In the same way you can add the 37972 to the Initial date like SELECT DATEADD(d, 37972, -2).

Upvotes: 1

knkarthick24
knkarthick24

Reputation: 3216

The problem is with your input value: => 01/01/0001

Immediate Workaround i can suggest is change your datatype in table from datetime to date 

select cast('01/01/0001' as date)
or
select convert(date,'01/01/0001')

Output: 0001-01-01

select cast('01/01/0001' as datetime)
or
select convert(datetime,'01/01/0001')

Output: Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93694

Try using ISDATE function which will tell you whether the given date is Valid or not.

SELECT CASE
         WHEN Isdate('10/25/2001') = 1 THEN CONVERT(varchar(20), '10/25/2001',102 )
         ELSE NULL
       END

Upvotes: 0

Related Questions