OK I am having a problem with DateFormat.
I am receiving a text string date from a csv and it looks like this when I get it:
20080314
When I use the DateFormat() Function it gets returned like this:
01/10/56878
Now am I just missing something? This is driving me nuts. Here is a sample of what I am doing
CODE
<CFIF FileExists("#csvTSSavePath#\#cvsTSFilename#.CSV")>
<cffile action="read" file="#csvTSSavePath#\#cvsTSFilename#.CSV" variable="csvFile">
<cfset csvFile = Replace(csvFile, chr(34), "", "all")>
<!--- Convert the lines to an array using the carriage return/line feed characters as delimiters --->
<cfset FileLines = listtoarray(ListFix(csvFile),"#chr(13)##chr(10)#")><!--- --->
<!--- Loop through the array of lines Use the number from="2" to eliminate the first row of data--->
<cfloop from="2" to="#arrayLen(FileLines)#" index="i">
<!--- Set Vrs --->
<cfset AUCTDATE = "#listgetat(FileLines[i],11)#">
<cfset DEFLTDAT = "#listgetat(FileLines[i],10)#">
<cfset PUBDATE = "#listgetat(FileLines[i],126)#">
<cfset RECDATE = "#listgetat(FileLines[i],125)#">
<cfset LOAN_DATE = "#listgetat(FileLines[i],3)#">
<cfset DATE_SOLD = "#listgetat(FileLines[i],72)#">
<!--- Checking for nulls --->
<CFIF AUCTDATE EQ "NULL"><cfset AUCTDATE = "NULL"><CFELSE><cfset AUCTDATE = "#DateFormat(listgetat(FileLines[i],11), "mm/dd/yyyy")#"></CFIF>
<CFIF DEFLTDAT EQ "NULL"><cfset DEFLTDAT = "NULL"><CFELSE><cfset DEFLTDAT = "#DateFormat(listgetat(FileLines[i],10), "mm/dd/yyyy")#"></CFIF>
<CFIF PUBDATE EQ "NULL"><cfset PUBDATE = "NULL"><CFELSE><cfset PUBDATE = "#DateFormat(listgetat(FileLines[i],126), "mm/dd/yyyy")#"></CFIF>
<CFIF RECDATE EQ "NULL"><cfset RECDATE = "NULL"><CFELSE><cfset RECDATE = "#DateFormat(listgetat(FileLines[i],125), "mm/dd/yyyy")#"></CFIF>
<CFIF LOAN_DATE EQ "NULL"><cfset LOAN_DATE = "NULL"><CFELSE><cfset LOAN_DATE = "#DateFormat(listgetat(FileLines[i],3), "mm/dd/yyyy")#"></CFIF>
<CFIF DATE_SOLD EQ "NULL"><cfset DATE_SOLD = "NULL"><CFELSE><cfset DATE_SOLD = "#DateFormat(listgetat(FileLines[i],72), "mm/dd/yyyy")#"></CFIF>
<!--- just outputting to see result --->
<cfoutput>
#AUCTDATE#<BR />
#DEFLTDAT#<BR />
#PUBDATE#<BR />
#AUCTDATE#<BR />
#RECDATE#<BR />
#LOAN_DATE#<BR />
#DATE_SOLD#<BR />
</cfoutput>
<!---This is the query--->
<!--- <cfquery name="insert_alameda_properties" datasource="#ds#">
INSERT INTO property_listings(ADDRESS, APN, AUCTADDR, AUCTDATE, AUCTTIME, BATHS, BEDS, CITY, COUNTYNM, COUNTYNO, DEFLTAMT, DEFLTDAT, DOCTYPE, HOUSENO, LOANAMT, LOTACRE, LVNGSQFT, MINBID, OWNERCTY, OWNERHNO, OWNERNM1, OWNERSNM, OWNERUNO, OWNERZIP, OWNRST, PP_SOURCE, PUBDATE, RECDATE, RECNO, STAGENM, STAGENO, STATE, STREETNM, THOMAS, TRSTNAME, TRSTPHON, YRBUILT, ZIPCODE, LOAN_DATE, LOAN_INST, TSTE_NUM, TRUSTOR, TSTR_LN_1, TSTR_FN_1, TRACT_NUM, DATE_SOLD, ROOMS, BLDG_SIZE, LAND_USE, LAND_VAL, BLDG_VAL, INST_NUM)
VALUES('#listgetat(FileLines[i],89)# #listgetat(FileLines[i],90)# #listgetat(FileLines[i],91)# #listgetat(FileLines[i],92)#', '#listgetat(FileLines[i],67)#', '#listgetat(FileLines[i],15)#', '#AUCTDATE#', '#listgetat(FileLines[i],12)#', '#listgetat(FileLines[i],105)#', '#listgetat(FileLines[i],104)#', '#listgetat(FileLines[i],94)#', 'Alameda', '06001', '#listgetat(FileLines[i],9)#', '#DEFLTDAT#', 'Notice of Trustees Sale', '#listgetat(FileLines[i],89)#', '#listgetat(FileLines[i],7)#', '#listgetat(FileLines[i],110)#', '#listgetat(FileLines[i],108)#', '#listgetat(FileLines[i],14)#', '#listgetat(FileLines[i],82)#', '#listgetat(FileLines[i],77)#', '#listgetat(FileLines[i],76)# #listgetat(FileLines[i],75)#', '#listgetat(FileLines[i],78)# #listgetat(FileLines[i],79)# #listgetat(FileLines[i],80)#', '#listgetat(FileLines[i],81)#', '#listgetat(FileLines[i],84)#', '#listgetat(FileLines[i],83)#', 'Redloc', '#PUBDATE#', '#RECDATE#', '#listgetat(FileLines[i],1)#', 'Trustee', '2', '#listgetat(FileLines[i],95)#', '#listgetat(FileLines[i],91)#', '#listgetat(FileLines[i],98)# #listgetat(FileLines[i],99)#', '#listgetat(FileLines[i],40)#', '#listgetat(FileLines[i],45)# #listgetat(FileLines[i],46)# #listgetat(FileLines[i],47)#', '#listgetat(FileLines[i],101)#', '#listgetat(FileLines[i],96)#', '#LOAN_DATE#', '#listgetat(FileLines[i],4)#', '#listgetat(FileLines[i],39)#', '#listgetat(FileLines[i],48)#', '#listgetat(FileLines[i],49)#', '#listgetat(FileLines[i],50)#', '#listgetat(FileLines[i],68)#', '#DATE_SOLD#', '#listgetat(FileLines[i],103)#', '#listgetat(FileLines[i],109)#', '#listgetat(FileLines[i],114)#', '#listgetat(FileLines[i],117)#', '#listgetat(FileLines[i],118)#', '#listgetat(FileLines[i],127)#')
</cfquery> --->
</cfloop>
<CFELSE>
</CFIF>
I am not using the query until I get the proper result from the VAR output, but I thought I would give you an idea of what I am trying to accomplish.
any help would be greatly appreciated. Thanks in advance
Dave
10 Mar, 2008 - 02:10 PM
|