griff77
asked on
Coldfusion parse date in select statement
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.
Any help is appreciated.
ASKER
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
This link might explain it better.
http://www.dbcsoftware.com/dbcfstechinfo.html
ASKER
Unfortunately, I have read-only access to the DB as well, so changing the structure is out of the question
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)>
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)>
ASKER
Formatting the data after the query is retrieved is not a problem. I was hope to do it right in the SELECT statement though.
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.
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.
>> 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.
Is there a list of database functions somewhere? The I could not find much googling ..
ASKER
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.
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
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
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
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
ASKER
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!
SUBSTRING results in Parsing error: keyword FROM expected in SUBSTRING operation.
Getting warmer!
ASKER
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)
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)
That's a new one! But won't you need the [FOR length] part to extract only the 2 digits of the month?
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?
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?
ASKER
|| 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?
>> 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 :)
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 :)
ASKER
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.
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?
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?
ASKER
Query has 5 columns and about 200 records....narrowed down from 4,790,000
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I have to head out now. But I'll check back tomorrow.
>> 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(yourQue