Solved

Coldfusion parse date in select statement

Posted on 2010-09-22
22
783 Views
Last Modified: 2013-12-24
I would like to  format a date in a coldfusion query.  The data is in a text field of length 8, and is formatted as YYYYMMDD.  I would like to parse  this in the select statement so the resulting query will have a field named 'mydate' formatted as MM/DD/YYYY.

Any help is appreciated.
0
Comment
Question by:griff77
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 9
22 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 33737023
If you're asking about database syntax, we need to know what database you're using.

>> The data is in a text field of length 8, and is formatted as YYYYMMDD

It's better to store dates in date/time fields, instead of text. Then you could simply use CF date functions to format it however you want.  

<input type="text" value="#DateFormat(yourQuery.myDate, "MM/DD/YYY")#" ...>
0
 

Author Comment

by:griff77
ID: 33737067
I'm using an odbc driver of type DB/C FS .  I think the data is flat-file based.

This link might explain it better.

http://www.dbcsoftware.com/dbcfstechinfo.html
0
 

Author Comment

by:griff77
ID: 33737085
Unfortunately, I have read-only access to the DB as well, so changing the structure is out of the question
0
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
LVL 52

Expert Comment

by:_agx_
ID: 33737165
Unfortunately, I can only tell you the syntax for db's like MS SQL, MySQL, Sybase, etc...  I don't know anything about DB/C FS.  

But you could also use CF's string functions.  If the field is always 8 characters, you could use something like:

(Not tested )
<cfset theNewValue = MID(myDate, 5, 2) &"/"& RIGHT(myDate, 2) &"/"& LEFT(myDate, 4)>



0
 

Author Comment

by:griff77
ID: 33737254
Formatting the data after the query is retrieved is not a problem.  I was hope to do it right in the SELECT statement though.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 33737299
BTW:  Most databases have _some_ version of those 3 string functions. (Though MID is often called substring).  So it's entirely possible yours does too. Meaning you could do the same thing within your query.  Unfortunately, I don't know the syntax for your db type.  
0
 
LVL 52

Expert Comment

by:_agx_
ID: 33737329
The timing of our posts clashed ...

>> I was hope to do it right in the SELECT statement though.

I know .. But while I'm familiar with a lot of db types, I'm not familiar with yours :( So I don't even know if your db supports those string functions, let alone the function names or syntax.

0
 
LVL 52

Expert Comment

by:_agx_
ID: 33737372
Is there a list of database functions somewhere? The I could not find much googling ..
0
 

Author Comment

by:griff77
ID: 33738352
All I know is that my driver is supposed to be ODBC 3.0 compatible.  I don't have access to any sort of function list.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 33738516
I would just go ahead and try some of the standard string functions then.  If they work, great. If not, you're no worse off than you are now.
http://msdn.microsoft.com/en-us/library/ms710249%28VS.85%29.aspx

ie I would try each separately just to see what works and what doesn't

    SELECT  LEFT(YourColumn, 4) AS TestValue FROM YourTable

... then
         SELECT  RIGHT(YourColumn, 2) AS TestValue FROM YourTable

... then
          SELECT  SUBSTRING(YourColumn, 5, 2) AS TestValue FROM YourTable

If they all work, next figure out what your db's concatenation operator is.  It's usually "&" or "+"

          SELECT   YourColumn  & ':::' AS TestConcat FROM YourTable

If by chance all of those work, rearrange them and construct your final query. Note: Substitute the correct concatenation operator if needed

      SELECT  SUBSTRING(YourColumn, 5, 2) &'/'&
                    RIGHT(YourColumn, 2)             &'/'&
                    LEFT(YourColumn, 4)  AS MyDate
      FROM     YourTable
0
 
LVL 52

Expert Comment

by:_agx_
ID: 33738599
Again, I'm flying blind on this one. But reading further into the ODBC docs suggest functions may be called differently. Try the syntax below as well.

ie  Try
     SELECT {fn SUBSTRING(YourColumn, 5, 2) } FROM YourTable

... as well as

     SELECT  SUBSTRING(YourColumn, 5, 2) FROM YourTable

http://msdn.microsoft.com/en-us/library/ms709434%28VS.85%29.aspx

0
 

Author Comment

by:griff77
ID: 33738940
RIGHT and LEFT both result in    Column not found: RIGHT or  Column not found: LEFT

SUBSTRING results in Parsing error: keyword FROM expected in SUBSTRING operation.

Getting warmer!
0
 

Author Comment

by:griff77
ID: 33739094
I figured it out...looked here:

http://troels.arvin.dk/db/rdbms/#functions-SUBSTRING

and found this syntax :  SUBSTRING(input FROM start-position [FOR length])

What worked for me was:   SUBSTRING(input FROM start-position)
0
 
LVL 52

Expert Comment

by:_agx_
ID: 33739127
That's a new one!  But won't you need the [FOR length] part to extract only the 2 digits of the month?  
0
 
LVL 52

Expert Comment

by:_agx_
ID: 33739181
That didn't come out right ;-) What I meant was, if the [FOR length] part works ... then you can obviously use SUBSTRING() instead of left(), right()

BTW: In case & and + don't work for concatenation, try using "||"

Also, just curious but did you have any luck with the { fn ....} style syntax or did it fail?  

0
 

Author Comment

by:griff77
ID: 33739833
|| did work to concatenate.  Thanks so much!  Final question....I have concatenated my value into a date such as 10/21/2010.  I am using a query of queries to pull Sum totals by week from the query that made the concatenated date.  My where clause states "WHERE #week(concatdate)# = #i# , where "i" is the index of a loop from 1 to 53.  The week function is being evaluated immediately though, so the clause only proves true when, for instance, 21=21.  How can I make it eval properly?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 33739946
>> The week function is being evaluated immediately though

Yeah, that's because it's being executed in CF, _before_ the QoQ. Short story, you can't do what you're trying to do in a QoQ.  They're VERY limited, and don't have any date function support at all.  The best you could do is a very, very ugly hack.  Essentially loop from 1 to 53 and UNION the results together.  

But before you resort to that ... you didn't answer my question about the the special function syntax { fn ...}. Did it work? The reason I ask is because I'm wondering if it provides access to other functions, beyond just the basics.

( BTW: Don't forget to post the final query for others who have the same question in the future :)
0
 

Author Comment

by:griff77
ID: 33740170
unfortunately,  { fn ...} didn't work.... I will post the working query, but do you have an alternative to my approach.  I'd like to query by numbered week, but may dates are text (20100901) and my database archaic.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 33740235
Darn.  

Thinking about it, I'm not even sure a UNION would work.  I think you would have to loop through the results calculating the totals as you loop.  Not very elegant, but unless your db has some version of the #week()# function, I can't think of any way to avoid it.

How many records AND columns are in the base query?
0
 

Author Comment

by:griff77
ID: 33740265
Query has 5 columns and about 200 records....narrowed down from 4,790,000
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 125 total points
ID: 33740401
If it's only 200 records, you could either accumulate the totals as you loop, and display the amounts only when the week changes  OR you could add a "Week" column to your query object. Then loop through your query to populate it. Then do your SUM in a QoQ.  Neither option is pretty. But given the limitations, I don't think you have much choice.

I don't have an example of the 1st option handy, but here's an example using the 2nd option

<!--- add a week column --->
<cfset queryAddColumn(yourQuery, "WeekNumber", [])>
<cfloop query="yourQuery">
        <!--- populate it --->
      <cfset yourQuery.WeekNumber[currentRow] = Week(yourDate)>
</cfloop>

<!--- SUM the results by WeekNumber --->
<cfquery name="newResults" dbtype="query">
      SELECT      WeekNumber, SUM(SomeAmount) AS TotalAmount
      FROM    yourQuery
      GROUP BY WeekNumber
</cfquery>
0
 
LVL 52

Expert Comment

by:_agx_
ID: 33740441
I have to head out now. But I'll check back tomorrow.
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Domino Website - Redirection 12 101
trying to resize an Image, using CFimage tag. 8 58
Detailed steps to upload 6 75
Application.cfm not found issue 2 39
When setting up new project requests for our site, one of the most powerful tools our team has available to use is Axure (http://www.axure.com/). It’s a tool for creating software and web prototypes that can function and interact as if it were the a…
Objective of This Article In 1990’s, when I was a budding software professional, I had a lot of confusion about which stream or technology, I had to choose to build my career. In those days, I had lot of confusion like whether to choose System so…
The purpose of this video is to demonstrate how to reset a WordPress password if you are locked out and cannot reset the password. A typical use would be if you cannot access the email to which WordPress would send the password recovery email to…
The purpose of this video is to demonstrate how to integrate Mailchimp with WordPress, by placing a Mailchimp signup form on a WordPress Page or Post. This will be demonstrated using a Windows 8 PC. Mailchimp will be used. Log into your Mailchi…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question