Help (Date)

• najeeb selk

  SSC Enthusiast

  Points: 103

  More actions

  March 22, 2006 at 12:30 pm

  #189916

  I have a table that have date column with some dates  such as

  DateCol	StringCol
  02/03/2006	Test1
  03/03/2006	Test2
  06/03/2006	Test3
  07/03/2006	Test4
  08/03/2006	Test5
  12/03/2006	Test6

  I want to select the date between 02/03/2006 and 12/03/2006 and the result must be as

  DateCol	StringCol
  02/03/2006	Test1
  03/03/2006	Test2
  04/03/2006	NULL
  05/03/2006	NULL
  06/03/2006	Test3
  07/03/2006	Test4
  08/03/2006	Test5
  09/03/2006	NULL
  10/03/2006	NULL
  11/03/2006	NULL
  12/03/2006	Test6

  How can I do that please?

  thanks

• [KH]

  SSCommitted

  Points: 1908

  More actions

  March 23, 2006 at 12:39 am

  #628283

  use LEFT JOIN

  declare@table table

  (

  DateColdatetime,

  StringColvarchar(10)

  )

  insert into @table

  select'2006-03-02', 'Test1'union all

  select'2006-03-03', 'Test2'union all

  select'2006-03-06', 'Test3'union all

  select'2006-03-07', 'Test4'union all

  select'2006-03-08', 'Test5'union all

  select'2006-03-12', 'Test6'

  declare@start_datedatetime,

  @end_datedatetime

  select@start_date= '2006-03-02',

  @end_date= '2006-03-12'

  selectdte, StringCol

  from

  (

  selectdateadd(day, n, @start_date) as dte

  from(

  select0 as n union all

  select1 as n union all

  select2 as n union all

  select3 as n union all

  select4 as n union all

  select5 as n union all

  select6 as n union all

  select7 as n union all

  select8 as n union all

  select9 as n union all

  select 10 as n

  ) a

  ) b

  left join

  @table t

  onb.dte= t.DateCol