Date function problem

[HPNAGA]

Table : "TableXYZ"
Column : "ColumnA"
User provides a value which is stored in variable "UserYear"

Needed Query : Select all fields from TableXYZ where year of columnA = UserYear

Query I came up with : select * from TableXYZ where year(columnA) = UserYear.

Query my boss want : He wants a query which does not have function on columnA to improve performance, so he wants something like

select * from TableXYZ where columnA = Date(useryear-month(columnA)-day(columnA))

Any suggestions how to achieve this..

Thanks in Advance

 

[blom0344]

Char or varchar input that is a valid string representation of a date or a timestamp (e.g.
"1997-12-23") is converted as is. "

{DB2 Cookbook by Graeme Birchall}

suggest storing the date in the variable as the string 'yyyy-mm-dd'

Ties Blom

 

[fredericofonseca]

select * from TableXYZ where columnA between @startdate and @enddate
where @startdate is a date made up of UserYear + 01 + 01 e.g. 2013-01-01
and @enddate is a date made up of UserYear + 12 + 31 e.g. 2013-12-31

That is assuming that ColumnA is a date field, not a datetime field



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886