change sql parameter to date decimal

Question

I have a SQL command in crystal reports (its OK if your not familiar with crystal) and I need to convert a date parameter to a decimal (to match a column in the database.)

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

IF I use a CAST on the above query it doesn't work:

SELECT decimaldate FROM TABLE1 WHERE decimaldate =
 CAST(CAST{?normaldate} AS VARCHAR) AS DECIMAL)

Answer 1

I suggest creating a formula (called something like @decimaldate) in formula to hold the equivalent numeric value of your date paramter - so it would be something like:

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

- then amend your selection criteria to select based on your new formula - like so:

SELECT decimaldate FROM TABLE1 WHERE decimaldate = {@decimaldate}

Answer 2

I think VARCHAR_FORMAT() is actually what you're looking for:

SELECT decimaldate
  FROM table1
 WHERE decimaldate = VARCHAR_FORMAT(@NormalDate, 'MMDDYYY')

You may have to wrap @NormalDate with DATE() to cast it to a date type (it depends on your input format).

Answer 3

Try something like this.

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

Or something like this where @normaldate is the search date.

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

Answer 4

This DB2 SQL function performs the date to MDY decimal conversion your query needs. Once it's created, your queries can compare a decimal column containing an MDY date to the output of UTIL.TO_DECIMAL_MDY( someValidDate )

CREATE OR REPLACE FUNCTION util.to_decimal_mdy(dateval DATE)
LANGUAGE SQL
RETURNS DECIMAL(9,0)
RETURN MONTH(dateval) * 10000000 + DAY(dateval) * 100000 + YEAR(dateval)
;