Query

• SA-1

  SSCrazy Eights

  Points: 8113

  More actions

  March 21, 2006 at 11:31 am

  #112059

  I have a table TableA with the following fields:

  RowId   BCode   dt_O   dt_Cl    Type

  1           0001    1/1/6   1/2/6    C

  2           0001     1/1/6   1/3/6    C

  4

  5

  6

   

  I need a query to return the diff between dt_O (rowid 2) and dt_Cl (rowid 1).

  Any suggestions.

  TIA

• RyanRandall

  SSChampion

  Points: 13623

  More actions

  March 21, 2006 at 12:17 pm

  #627954

  Something like this?

  declare @t table (RowId int, BCode varchar(4), dt_O datetime, dt_Cl datetime, Type char(1))

  insert @t

            select 1, '0001', '1/1/6', '1/2/6', 'C'

  union all select 2, '0001', '1/1/6', '1/3/6', 'C'

  select datediff(d, b.dt_O, a.dt_Cl) from @t a inner join @t b on a.RowId = 1 and b.RowId = 2

  Ryan Randall

  Solutions are easy. Understanding the problem, now, that's the hard part.

• SQLBill

  SSC Guru

  Points: 51440

  More actions

  March 22, 2006 at 7:04 am

  #628079

  You will also have to let SQL Server know how to interpret those dates. Is 1/3/6 - January 3, 2006 or 1 March 2006, or January 3, 1996 or 2001 March 6th, or something else.

  To tell SQL Server what each value is, use SET DATEFORMAT

  SET DATEFORMAT DMY --day first

  or

  SET DATEFORMAT MDY --month first

  -SQLBill