473,320 Members | 2,162 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

DateFormat() in the twilight zone

zigman68
I have a problem with the DateFormat() Function that is driving me nuts!
I am downloading a csv and then parsing through it, then inserting into a SQL 2005 DB. Here is the problem. ...
A number of fields that are passed are strings representing dates.
E.G. 20080324
( which I read as 2008/03/24)
Sometimes they are there sometimes they are not.
When I perform a DateFormat on them to set them in my VAR, this is what I am getting back for the same data:
01/20/56878

Now I may be missing something but those are not even the same numbers.



Here is a sample of my code:


Expand|Select|Wrap|Line Numbers
  1. <CFIF FileExists("#csvTSSavePath#\#cvsTSFilename#.CSV")>
  2. <cffile action="read" file="#csvTSSavePath#\#cvsTSFilename#.CSV" variable="csvFile">
  3. <cfset csvFile = Replace(csvFile, chr(34), "", "all")>
  4. <!--- Convert the lines to an array using the carriage return/line feed characters as delimiters --->
  5.     <cfset FileLines = listtoarray(ListFix(csvFile),"#chr(13)##chr(10)#")>
  6. <!--- Loop through the array of lines Use the number from="2" to eliminate the first row of data--->
  7.         <cfloop from="2" to="#arrayLen(FileLines)#" index="i">
  8.             <cfset AUCTDATE = "#listgetat(FileLines[i],11)#">
  9.             <cfset DEFLTDAT = "#listgetat(FileLines[i],10)#">
  10.             <cfset PUBDATE = "#listgetat(FileLines[i],126)#">
  11.             <cfset RECDATE = "#listgetat(FileLines[i],125)#">
  12.             <cfset LOAN_DATE = "#listgetat(FileLines[i],3)#">
  13.             <cfset DATE_SOLD = "#listgetat(FileLines[i],72)#">
  14.             <CFIF AUCTDATE EQ "NULL"><cfset AUCTDATE = "NULL"><CFELSE><cfset AUCTDATE = "#DateFormat(listgetat(FileLines[i],11), "mm/dd/yyyy")#"></CFIF>
  15.             <CFIF DEFLTDAT EQ "NULL"><cfset DEFLTDAT = "NULL"><CFELSE><cfset DEFLTDAT = "#DateFormat(listgetat(FileLines[i],10), "mm/dd/yyyy")#"></CFIF>
  16.             <!--- <CFIF PUBDATE EQ "NULL"><cfset PUBDATE = "NULL"><CFELSE><cfset PUBDATE = "#DateFormat(listgetat(FileLines[i],126), "mm/dd/yyyy")#"></CFIF> --->
  17.             <CFIF RECDATE EQ "NULL"><cfset RECDATE = "NULL"><CFELSE><cfset RECDATE = "#DateFormat(listgetat(FileLines[i],125), "mm/dd/yyyy")#"></CFIF>
  18.             <CFIF LOAN_DATE EQ "NULL"><cfset LOAN_DATE = "NULL"><CFELSE><cfset LOAN_DATE = "#DateFormat(listgetat(FileLines[i],3), "mm/dd/yyyy")#"></CFIF>
  19.             <CFIF DATE_SOLD EQ "NULL"><cfset DATE_SOLD = "NULL"><CFELSE><cfset DATE_SOLD = "#DateFormat(listgetat(FileLines[i],72), "mm/dd/yyyy")#"></CFIF>
  20.  
  21.  
  22.             <cfoutput>
  23.                 #AUCTDATE#<BR />
  24.                 #DEFLTDAT#<BR />
  25.                 #PUBDATE#<BR />
  26.                 #AUCTDATE#<BR />
  27.                 #RECDATE#<BR />
  28.                 #LOAN_DATE#<BR />
  29.                 #DATE_SOLD#<BR />
  30.             </cfoutput>


Any help would be much apreciated. Thanks in advance.
Mar 11 '08 #1
1 3197
acoder
16,027 Expert Mod 8TB
Welcome to TSDN!

The date is a string, not a date/time object which is what DateFormat expects for the first parameter. If you're passing it as a string, it must be within quotes, or you can use CreateDate() to create a date/time object.
Mar 11 '08 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: MLH | last post by:
I would like to be able to look up any 5-digit ZIP in a table that would show the correct time zone for the area. For example, I would like to look up 91915 in the table and see something that...
3
by: Jon Davis | last post by:
The date string: "Thu, 17 Jul 2003 12:35:18 PST" The problem: // this fails on PST DateTime myDate = DateTime.Parse("Thu, 17 Jul 2003 12:35:18 PST"); Help? Jon
0
by: Phil | last post by:
My regional setting (on Win2000) is: (General Tab)Locale = English (US) (Date Tab), short date = MM/dd/yy When I ask .Net DateFormat.ShortDatePattern, I get MM/dd/yyyy Now, where does the...
1
by: Clodoaldo Pinto Neto | last post by:
Hi all, I want to have the time zone string (like 'BRT') displayed after a date-time. The date column is of the type timestamp with time zone. But the time zone is not displayed. It works for...
4
by: newtophp2000 | last post by:
Hello, I receive a file containing some character fields along with a date. The date values in the file are formatted as "dd/mm/yy", that is 2-digit day, 2-digit month, and 2-digit year. The...
3
by: Satish Itty | last post by:
Hi all, I have a big problem in my hands and not sure how I can fix this. Any suggestions would be greatly appreciated. I have a .NET 3 tier app developed in VS2003 and .NET 1.1. the client is a...
0
by: pankajol82 | last post by:
Hi all I am using following code to change regional setting at run time its not giving any error but at the same time the format of date is not changing. Dim DateFormat As New...
7
by: Steve | last post by:
Hi All I have a windows application written in VB.net 2005 The users have to select a State of Australia, which I use to check they have the correct windows time zone selected in control panel...
3
dzenanz
by: dzenanz | last post by:
String input="4/3/08 9:57 AM"; DateFormat df = DateFormat.getDateInstance(DateFormat.SHORT); StartDate = df.parse(input); out.println(StartDate.toLocaleString());//"Apr 3, 2008 12:00:00 AM" ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.