SQL Concat help

[Jawa]

Can anyone tell me how I can achieve the following. Right now I am getting an error that PubYear is not a valid column. Thank you!

SELECT AdINFO_Publication,
ADINFO_PUBLICATIONDATE_YEAR+ '-' + ADINFO_PUBLICATIONDATE_MONTH + '-01' AS PubYear
FROM crAdInfo
AND PubYear BETWEEN #dateformat(startdate,"yyyy-mm-dd")# AND #dateformat(enddate,"yyyy-mm-dd")#

Any Help is appreciated

 

[cfStarlight]

You should cast the value as a datetime and use cfqueryparam. But something like this might work.

Though I don't know if between will give the expected results when used on a varchar column

SELECT        
   AdINFO_Publication,  
   ADINFO_PUBLICATIONDATE_YEAR+ '-' +      
     ADINFO_PUBLICATIONDATE_MONTH +  
     '-01' AS PubYear
FROM    crAdInfo
WHERE  ADINFO_PUBLICATIONDATE_YEAR+ '-' + 
       ADINFO_PUBLICATIONDATE_MONTH +  '-01' BETWEEN '#dateformat(startdate,"yyyy-mm-dd")#' AND '#dateformat(enddate,"yyyy-mm-dd")#'

 

[r937]

BETWEEN works just fine on VARCHAR, and it will work correctly on date strings only if the values are formatted in year-month-day sequence

jawa, to avoid having to type out the expression more than once, you can wrap your query in an outer SELECT...

[blue]SELECT * FROM ([/blue]
SELECT AdINFO_Publication
     , ADINFO_PUBLICATIONDATE_YEAR + '-' + 
       ADINFO_PUBLICATIONDATE_MONTH +  '-01' AS PubYear
  FROM crAdInfo [blue])
 WHERE PubYear BETWEEN '#dateformat(startdate,"yyyy-mm-dd")#' 
                   AND '#dateformat(enddate,"yyyy-mm-dd")#'[/blue]

note that date strings must be enclosed in quotes


r937.com | rudy.ca

 

[cfStarlight]

Creating a derived query that pulls all records in the table, just to save some typing doesn't make sense to me. I suspect the query would be more efficient if you repeat the columns in the where clause, especially if your table contains a large number of rows.

 

[Jawa]

cfStarlight THANK YOU! WOKRED LIKE A CHARM!!!!

THANK YOU VERY VERY MUCH!!!

 

[cfStarlight]

Glad its working Jawa.

Curious, how the previous 3 posts disappeared.