search only date

[test 09]

select a.*, b.ConstructionCompletionDate_dt from projects a join schedule b on a.projectid=b.projectid where projectname like @projectname or country like @country or clientcompany like @clientcompany or ConstructionCompletionDate_dt like ('%(@ConstructionCompletionDate_dt)%')

this is how im searching.. where ConstructionCompletionDate_dt isonly yyyy from datetime field.. if im using '% %' for example if i search 2010,, all fields with 012 in mm/dd/yyyy are displaying... i want only rows with year 2010 how can i do..

[Lost User]

test-09 wrote:

where ConstructionCompletionDate_dt isonly yyyy from datetime field

A real DateTime field that you converted to a VARCHAR(4)? The LIKE operator is more suitable to search through text-fields. How about something like this;

SELECT *
FROM HumanResources.Employee
WHERE YEAR([BirthDate]) = 1972

I are Troll :suss:

[Mycroft Holmes]

Tell me you are storing your dates as datetime and not varchar. Try the datepart keyword, something like

where datepart(yyyy,ConstructionDate) = 2010

Never underestimate the power of human stupidity RAH

[test 09]

im storing it as varchar as this is comfortable to my requirement..

[dan sh]

You should use the correct data type then. Why are you not using Date as the datatype? You will be doing some casts and converts in your application which is error prone.

[PIEBALDconsult]

Get comfortable with doing it the right way. Now!

[Mycroft Holmes]

test-09 wrote:

m storing it as varchar

This is the most basic error in data design, I recommend that you change your data type from varchar to datetime NOW The longer you delay the more work it will take to change. You will change eventually or the project will die, the downstream cost of this mistake is extreme and must be fixed immediately.

Never underestimate the power of human stupidity RAH

[Luc Pattyn]

I second that. :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

---

Getting an article published on CodeProject should be easier and faster.

---