December 2, 2009 at 1:20 pm
Hello experts,
I’m trying to write a simple query using wildcard character % but not getting the results at all. Query gets executes though without any rows. I’m not sure can I use wildcard on a column that data type is datetime, can I? However when I use wildcard of other column whose data type is varchar it works fine. For reference here is my query
select ColNumber, DbName, TbName, ColName, capturedDate, ColLastModifiedDate
from TotalColumn
where DbName like 'CIAdeel' and CapturedDate like '2009-12-02%' Order by ColLastModifiedDate
And here is table structure
CREATE TABLE [dbo].[TotalColumn](
[ColNumber] [bigint] IDENTITY(1,100000) NOT NULL,
[CapturedDate] [datetime] NOT NULL,
[ColName] [varchar](300) NOT NULL,
[TbName] [varchar](500) NOT NULL,
[DbName] [varchar](60) NOT NULL,
[ColId] [int] NULL CONSTRAINT [DF_TotalColumn_ColId] DEFAULT ((10)),
[ColType] [varchar](20) NULL,
[ColCreationDate] [datetime] NULL,
[ColLastModifiedDate] [datetime] NULL,
[ColLength] [varchar](20) NULL,
Can somebody tell me what’s wrong and how I can get the just the date without time because I need to compare this date with some other date. Time is different between this date and the other column which I’ve to use for compare.
Thanks a lot in advance.
December 2, 2009 at 1:25 pm
"Like" and the related wildcard characters only work on string data types, not on numbers or dates.
The way to do what you need is to assign the date to a datetime variable, and then test like this:
and
CapturedDate >= @DateVariable
and
CapturedDate < dateadd(day, 1, @DateVariable)
That will get you all the datetime values for the date you want.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply