converting back string to date 1

[mrdance]

I want to search for dates in a date-type field. I would like to do a LIKE search for strings containing 2001-01, or 2001-01-01 for example. I can search for 2001 and it returns result but not when I search for more. Do I have to convert it back to a date format and how do I do that?

Thanks / Henrik

 

[rajeevnandanmishra]

Hi Henrik,

We don't have any function in SQL which return the date in yyyy-mm-dd format. But we have a function to give yyyy.mm.dd
So if you pass your date as 2001.01% or 2001.01.1% this is possible by following syntax

where convert(char(10),myDateColumn,102) like '2001.05%'

 

[tlbroadbent]

You can search for the string with "-" if you use the REPLACE function.

WHERE REPLACE(convert(char(10),myDateColumn,102),'.','-') LIKE '2001-05%' Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[mrdance]

Thanks, but I can't pass '2001.06.21%'

// Henrik

 

[mrdance]

Thank you, now it works great!