Problem inserting/formatting "date" values

[gadjodilo77]

Hello,

I have made a script that uploads a csv file into a database table. De data is directly inserted in a temporary table. The second step is then after uploading it in the temporary table to insert the data into another table. However I have a date field in the other table and the date which were in the csv file and thus now in the temporary table is like this: Wed Nov 09 12:17:15 CET 2005 What I really want is to insert it like this: 2005-11-09

I made a script which makes a sql query (temporary table) and lists the date field(zen.z_scandatum) and then changes this value of Wed Nov 09 12:17:15 CET 2005 into 2005-11-09:

It works like this:

<cfif zen.recordcount GT 0>
<cfset temp = ValueList(zen.z_scandatum)>
<CFLOOP INDEX="Teller" FROM=1 TO=#ListLen(temp)#>
<cfset myString = ListGetAt(temp,Teller)>
<cfswitch expression="#Len(Trim(myString))#">
<cfcase value="28"><!--- vb: BER-4999--->
<cfset maand = Mid(myString,5,3)>
<cfif IsDefined("maand") AND #maand# EQ "Jan"><cfset maand = 01>
<cfelseif IsDefined("maand") AND #maand# EQ "Feb"><cfset maand = 02>
<cfelseif IsDefined("maand") AND #maand# EQ "Mar"><cfset maand = 03>
<cfelseif IsDefined("maand") AND #maand# EQ "Apr"><cfset maand = 04>
<cfelseif IsDefined("maand") AND #maand# EQ "May"><cfset maand = 05>
<cfelseif IsDefined("maand") AND #maand# EQ "Jun"><cfset maand = 06>
<cfelseif IsDefined("maand") AND #maand# EQ "Jul"><cfset maand = 07>
<cfelseif IsDefined("maand") AND #maand# EQ "Aug"><cfset maand = 08>
<cfelseif IsDefined("maand") AND #maand# EQ "Sep"><cfset maand = 09>
<cfelseif IsDefined("maand") AND #maand# EQ "Oct"><cfset maand = 10>
<cfelseif IsDefined("maand") AND #maand# EQ "Nov"><cfset maand = 11>
<cfelseif IsDefined("maand") AND #maand# EQ "Dec"><cfset maand = 12>
</cfif>
<cfset dag = MID(myString,9,2)>
<cfset jaar = LSParseNumber(Right(myString, 4)) >

</cfcase>
<cfcase value="23">
<cfset maand = Mid(myString,4,3)>
<cfif IsDefined("maand") AND #maand# EQ "Jan"><cfset maand = 01>
<cfelseif IsDefined("maand") AND #maand# EQ "Feb"><cfset maand = 02>
<cfelseif IsDefined("maand") AND #maand# EQ "Mar"><cfset maand = 03>
<cfelseif IsDefined("maand") AND #maand# EQ "Apr"><cfset maand = 04>
<cfelseif IsDefined("maand") AND #maand# EQ "May"><cfset maand = 05>
<cfelseif IsDefined("maand") AND #maand# EQ "Jun"><cfset maand = 06>
<cfelseif IsDefined("maand") AND #maand# EQ "Jul"><cfset maand = 07>
<cfelseif IsDefined("maand") AND #maand# EQ "Aug"><cfset maand = 08>
<cfelseif IsDefined("maand") AND #maand# EQ "Sep"><cfset maand = 09>
<cfelseif IsDefined("maand") AND #maand# EQ "Oct"><cfset maand = 10>
<cfelseif IsDefined("maand") AND #maand# EQ "Nov"><cfset maand = 11>
<cfelseif IsDefined("maand") AND #maand# EQ "Dec"><cfset maand = 12>
</cfif>
<cfset dag = MID(myString,7,2)>
<cfset jaar = LSParseNumber(Right(myString, 4)) >


</cfcase>
<cfdefaultcase>
<cfset maand = 00 >
<cfset dag = 00>
<cfset jaar = 0000>
<cfset datum = #jaar#-#maand#-#dag#>
</cfdefaultcase>
</cfswitch>
<CFQUERY NAME="update" DATASOURCE="rug">
INSERT INTO dev (dev_serie, scandate)
SELECT '#zen.z_serienr#','#jaar#-#maand#-#dag#'
</CFQUERY>
</cfloop>
</cfif>

As you see it works fine for the scandate value to be inserted in the right way into the database table dev. However I also want to insert the zen.z_serienr into the dev_serie field of the dev table. Actually insert the dev_serie that belongs to the good scandate in the same row!

Now It just inserts the first value of zen.z_serienr for each scandate insert. This is because zen.z_serienr is not in a/the list or something. But is there a possibility to get this in my table in a good way?

A solution would be (I guess) to do it in two steps:

First make a list of the zen.z_serienr and insert each value into the new table (by using loop).
And then a update of this table and try to insert the scandate in a 2006-12-01 way in the right row...

But how can I do this? I really don't hav an Idea.

Is there somebody who could help me with this?

Thank you,

Kabbi

(The reason I want the date in the table like this; 2006-09-12 is that later on the dates has to be compared to the dates in another table)