Query assistance

• Sherri Barkley

  SSCrazy

  Points: 2269

  More actions

  September 1, 2010 at 1:55 pm

  #142526

  I am working on a report that will have a date parameter and I want to see any records where a person has days off on that day of the week. It is a vendor supplied database so I am not able to change the way they store the data. Here is a sample of what I might be dealing with and I know I am missing something (probably simple) but I can't see what I am missing.

  create table #TempDaysOff (daysoff varchar(10))

  insert into #TempDaysOff values('1,7')

  insert into #TempDaysOff values('2,3,4')

  insert into #TempDaysOff values('4,5')

  insert into #TempDaysOff values('4,5,6')

  insert into #TempDaysOff values('6,7')

  declare @date as datetime

  declare @DW as varchar(6)

  set @date = '09-01-2010'

  set @DW = '''%' + cast(datepart(dw,@date) as varchar(1)) + '%'''

  select daysoff, @DW

  from #TempDaysOff

  where daysoff like @DW

  My goal is to only get the records that have a 4 in the daysoff field since the way my SQL Server is configured 4 is Wednesday. But with the query above it doesn't return any records even though if I take out the where clause I can see that the @DW = '%4%'. Does anyone have any advise on what I am doing incorrectly?

• David Webb-CDS

  SSCoach

  Points: 17398

  More actions

  September 1, 2010 at 2:03 pm

  #1215276

  How about:

  declare @date as datetime

  declare @DW as varchar(6)

  set @date = '09-01-2010'

  set @DW = cast(datepart(dw,@date) as varchar(1))

  select @DW

  select daysoff, @DW

  from #TempDaysOff

  where daysoff like '%' + @DW + '%'

  ---

  And then again, I might be wrong ...
  David Webb

• Sherri Barkley

  SSCrazy

  Points: 2269

  More actions

  September 1, 2010 at 2:07 pm

  #1215277

  Thank you. I thought I tried that, but I must not have tried that combination. :blush: