insert '00-jan-00' as a date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

insert '00-jan-00' as a date

Author

Topic

clir
Starting Member

6 Posts

Posted - 2005-06-07 : 17:35:42

Hello,

i have a table with several columns with datatype DateTime.
I insert data from a csv file into this table on a regular basis. I sometimes get an error when there's a date '00-jan-00' in the csv file. This is apparently not recognised as a date in SQL Server and it obviously cancels the transaction (cannot convert error message).
I was thinking of using a trigger (I use SQL Server7) to replace '00-jan-00' by NULL on the fly but it does not work(still error message):

create trigger mytrigger_insert
ON mytable
FOR INSERT
AS
INSERT CASE WHEN DUE_DATE='00-jan-00' THEN NULL ELSE DUE_DATE END,
CASE WHEN AMEND_DATE='00-jan-00' THEN NULL ELSE AMEND_DATE END,EFF_COMP_DATE
FROM inserted

what is wrong with this trigger?
Is a trigger appropriate?

Otherwise what could be a solution to insert '00-jan-00' in a dateTime column?

Thx

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-06-07 : 18:04:02

I would recommend that you insert your CSV data into a different table where the date goes into a VARCHAR column, and then use a SQL CASE statement to filter out the bad data before you insert it into the real table.

I am a little intrigued by this statement:
"...I sometimes get an error when there's a date '00-jan-00' in the csv file. This is apparently not recognized as a date in SQL Server…" Is this supposed to be a valid date? What calendar has a day of month of zero?

CODO ERGO SUM

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-06-07 : 18:10:31

Are you using DTS? Another way could be to use an ActiveX script in the transformations


Function Main()
	theDate = DTSSource("csvColumn")
	If theDate = "00-jan-00" Then
	DTSDestination("YourDateColumn") = NULL
	Else
	DTSDestination("YourDateColumn") = DTSSource("csvColumn")
	End IF
	Main = DTSTransformStat_OK
End Function

I would probably go with MVJ using an import table

Andy

Beauty is in the eyes of the beerholder

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-06-07 : 22:55:31

why not replace the value in the csv?

open it in a text editor like notepad and replace the value

for prevention of the error and tedious work, make sure that the csv file will not contain invalid date formats

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources