将sql参数更改为date decimal。

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.)

我在crystal reports中有一个SQL命令(如果您不熟悉crystal的话，它是OK的)，我需要将一个日期参数转换为decimal(以匹配数据库中的一个列)。

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)

4 个解决方案

#1

Try something like this.

这样的尝试。

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

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

或者像这样@normaldate是搜索日期。

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

#2

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:

我建议在公式中创建一个公式(称为@decimaldate)，以保持日期参数的等效数值——所以应该是这样的:

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}

#3

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

我认为VARCHAR_FORMAT()实际上是您要查找的:

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).

您可能需要用DATE()包装@NormalDate以将其转换为日期类型(这取决于您的输入格式)。

#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 )

这个DB2 SQL函数执行到查询所需的MDY十进制转换的日期。一旦创建了它，您的查询就可以比较一个包含MDY日期的小数列和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)
;

#1