Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2009
    Posts
    28

    Unanswered: Can wildcards be used on datetime column?

    Hello experts,

    Im trying to write a simple query using wildcard character % but not getting the results at all. Query gets executes though without any rows. Im 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 whats 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 Ive to use for compare.

    Thanks a lot in advance.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    CapturedDate BETWEEN '2009-12-02' AND DATEDIFF(ss,1,'2009-12-03')

    ???
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Short answer. No.

    If the time portion of the data is not needed, then it should not be stored. If it is needed, then you will need to do something like
    Code:
    where convert(varchar(10), CapturedDate , 101) = '12/02/2009'
    One side question, though. Do you really increment your identity column by 100,000?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    DECLARE @d		DATETIME	-- Used in place of date argument
    ,  @d1			DATETIME	-- Earliest date supported by MS-SQL
    
    SET @d = GetDate()
    SET @d1 = '1753-01-01'
    
    SELECT ColNumber, DbName, TbName
    ,  ColName, capturedDate, ColLastModifiedDate 
       FROM TotalColumn
       WHERE  DbName LIKE 'CIAdeel%'
          AND CapturedDate <= DateAdd(day, DateDiff(day, @d1, @d), @d1)
          AND DateAdd(day, 1 + DateDiff(day, @d1, @d), @d1) < CapturedDate
       ORDER BY ColLastModifiedDate
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jan 2003
    Location
    British Columbia
    Posts
    44
    I’m not sure can I use wildcard on a column that data type is datetime, can I?
    Yes you can, but it is a bit of a pain.

    The default date format in SQL Server is mon dd yyyy hh:miAM (or PM).
    The Like clause needs to be written with that in mind.

    Like '&#37;1997%' - all records in 1997
    Like 'Jul%1997%' - all records in Jul 1997.
    Like 'Jul 11%' - all records on Jul 11 any year
    Like 'Jul 9%' - all records on Jul 9 any year

    For your example of CapturedDate like '2009-12-02%' you need to use CapturedDate Like 'Dec 2 2009%'

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    -1...I meant -1
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by brucevde View Post
    Yes you can, but it is a bit of a pain.

    The default date format in SQL Server is mon dd yyyy hh:miAM (or PM).
    The Like clause needs to be written with that in mind.

    Like '%1997%' - all records in 1997
    Like 'Jul%1997%' - all records in Jul 1997.
    Like 'Jul 11%' - all records on Jul 11 any year
    Like 'Jul 9%' - all records on Jul 9 any year

    For your example of CapturedDate like '2009-12-02%' you need to use CapturedDate Like 'Dec 2 2009%'
    Cough...cough...that's some good sheet you got there...

    I love you Man
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •