DateFormat() has landed in the twilight zone

DateFormat is changing the data?

Page 1 of 1

1 Replies - 2012 Views - Last Post: 17 March 2008 - 05:09 AM Rate Topic: -----

#1 zigman68  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 06-March 08

DateFormat() has landed in the twilight zone

Posted 10 March 2008 - 02:10 PM

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

<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 :crazy:

Is This A Good Question/Topic? 0
  • +

Replies To: DateFormat() has landed in the twilight zone

#2 sansclue  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: DateFormat() has landed in the twilight zone

Posted 17 March 2008 - 05:09 AM

View Postzigman68, on 10 Mar, 2008 - 02:10 PM, said:

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


CF is treating 20080314 as a numeric representation of a date. That is different than the date string "2008-03-14". That is why the results are not what you are expecting.

Re-format the string as something CF recognizes as a date string, like "yyyy-mm-dd"

<cfset newDateString = left(theDate,4) &"-"& mid(theDate,5,2) &"-"& right(theDate,2)>


Was This Post Helpful? 1
  • +
  • -

Page 1 of 1