Can wildcards be used on datetime column?

  • 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.

  • "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