sqlsql-servercrystal-reportscrystal-reports-2008crystal-reports-2010

Concatenation of dates

How can I concatenate year, month and day in order to use it in where clause of query?

I tried to do that but it bring error message, The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. SQL State: 22007

Select * 
from table_name 
where DocDate >= cast(YEAR({?dateto}) as varchar(4)) + '-02-' + cast(DAY({?dateto}) as varchar(2))

Please anyone can help me

Solution

  • Why would you do that? Just use datefromparts():

    where docdate >= datefromparts(YEAR({?dateto}), 2, DAY({?dateto}))

    Of course, February has only 28 or 29 days, so you might need to take this into account:

    where docdate >= datefromparts(YEAR({?dateto}), 2,
                               (CASE WHEN DAY({?dateto}) > 28 THEN 28 ELSE DAY({?dateto}) END)
                              )