September 15, 2006 at 12:37 pm
ok perhaps I am going crazy but from everything I have read about sql server this query should not work
select * from FTBuEmployeeEvaluation
where [date] = '1999-03-05'
where [date] = hiredate and it's a datetime field. As you can see the date is passed in as a string. But why am i getting one row back from the table. I am completly baffeled. In Oracle I would have written this query like this:
select * from FTBuEmployeeEvaluation
where [date] = to_date('1999-03-05','yyyy-mm-dd') and I would have been all set.
The string compoarion of the date should not work.
Any ideas.
Sameer
September 15, 2006 at 1:08 pm
Read BOL on datetime datatypes...
SQL Server recognizes date and time data enclosed in single quotation marks (') in these formats:
Alphabetic date formats (for example, 'April 15, 1998')
Numeric date formats (for example, '4/15/1998')
Unseparated string formats (for example, '19981207')
**ASCII stupid question, get a stupid ANSI !!!**
September 15, 2006 at 2:03 pm
Sameer,
It could be cos the Date time field stores Time as well so for example 1999-03-05 11:00:00.000 might not be picked up by your query so try this:
select * from FTBuEmployeeEvaluation
where Cast(Convert(Char(10),[date],101) as Datetime) = Cast('1999-03-05' as datetime)
Thanks
Sreejith
September 15, 2006 at 2:07 pm
Go tit..it's surprising to me how many times while typing Got it...I somehow manage to type Go tit. Wonder where my mind is...Anyway, yes indeed the datetime column is not storing the time portion and thats why my query is working.
I dont like that at all. Sql server should have treated a string like a string, regardless of what the columns datatype is. I guess if they treated a patently obvious thing the way it should be it would be too easy.
Thanks every one.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply