I've got a SQL command in very reviews (its OK in case your unfamiliar with very) and I have to convert to start dating ? parameter to some decimal (to complement a column within the database.)

SELECT decimaldate FROM TABLE1 WHERE decimaldate = {?normaldate} 
--ex: 12/01/2011 needs to become 12012011

Basically make use of a CAST around the above query it does not work:

SELECT decimaldate FROM TABLE1 WHERE decimaldate =

Try something similar to this.

 select CAST(replace(convert(varchar, getdate(), 101), '/', '') AS DECIMAL)

Or something like that such as this where @normaldate may be the search date.

SELECT decimaldate FROM TABLE1 WHERE decimaldate = CAST(replace(convert(varchar, @normaldate, 101), '/', '') AS DECIMAL)

I would recommend developing a formula (known as something similar to @decimaldate) in formula to carry the same number worth of your date paramter - so it might be something similar to:

year({?normaldate})*10000 + month({?normaldate})*100 + day({?normaldate})

- then amend your selection criteria to choose according to your brand-new formula - like so:

SELECT decimaldate FROM TABLE1 WHERE decimaldate = {@decimaldate}

I believe [cde] is really what you are searching for:


You might want to wrap SELECT decimaldate FROM table1 WHERE decimaldate = VARCHAR_FORMAT(@NormalDate, 'MMDDYYY') with @NormalDate to cast it to some date type (it is dependent in your input format).

This DB2 SQL function works the date to MDY decimal conversion your query needs. Once it's produced, your queries compares a decimal column that contains an MDY date towards the creation of UTIL.TO_DECIMAL_MDY( someValidDate )