SQL Server Performance

SELECT with WHERE clause LIKE with hyphen

Discussion in 'SQL Server 2008 General DBA Questions' started by klehonia, Mar 30, 2010.

  1. klehonia New Member

    Hi everyone,
    i'm trying to script this: SELECT *
    FROM [msdb].[dbo].[backupset]
    where backup_start_date like '2010-01-%'
    but i think the sql doesn't understand hyphen '-' as a character (backup_start_date is a column datetime data type). How can i write this?
    thanks in advance.
  2. L0st_Pr0phet Member

    Why dont you just do the following: SELECT * FROM [msdb].[dbo].[backupset] where backup_start_date >'2010-01-01'
    OR
    SELECT * FROM [msdb].[dbo].[backupset] where backup_start_date between '2010-01-01' and '2010-01-31'
  3. klehonia New Member

    This was very helpful. I wrote it like this to get results of the today day:
    DECLARE @dt as DATE
    SET @dt = getdate()
    SELECT *
    FROM [msdb].[dbo].[backupset]
    where backup_start_date>=(select @dt)
    Thank you very much.
  4. Adriaan New Member

    Huh?
    where backup_start_date>=(select @dt)
    Interesting abuse of syntax!
    You can safely drop the stuff I marked in red, above.
  5. Madhivanan Moderator

    Note that date col has time too, so use
    SELECT *
    FROM [msdb].[dbo].[backupset]
    where backup_start_date>=dateadd(day,datediff(day,0,getdate()),0)
  6. klehonia New Member

    If you run my script you will see that brings you only the date without time, sec etc.
    Thank you anyway.
  7. Adriaan New Member

    Actually Madhivanan is pointing out an important issue with your script.
    (1) The GETDATE() function includes the time-of-day, so your >= filter will not find backups that started earlier today - so the query is unlikely to find any matching rows.
    (2) The backup_start_date column of the backupset table in msdb includes the time-of-day as well. This is why you need Madhivanan's expression, which indeed sets the timepart of today's date to midnight (start of the day). Now the query will find all backups that started since midnight.
  8. L0st_Pr0phet Member

    Why dont you just do the following:SELECT * FROM [msdb].[dbo].[backupset] where backup_start_date >'2010-01-01'

Share This Page