SQL Query Invoice Date + Convert to days(5) = Due Date

• c.vaibhav

  SSChasing Mays

  Points: 652

  More actions

  July 31, 2009 at 4:09 am

  #137546

  Hi Friends,

  I want a select query that will add n number of days to Invoice Date and give me the Due date..

  my table contains Invoice Date and Payment terms(this contain the integer that is to be added to invoice date)..

  I hope this could be done..

  Thanks..

  Vaibhav

• c.vaibhav

  SSChasing Mays

  Points: 652

  More actions

  July 31, 2009 at 4:17 am

  #1032842

  okay.. i got the following query..

  DateAdd("d", PaymentTerms, InvoiceDate)

  Here "d" represents day.. Payment terms represents number of days that needs to be added to invoicedate.. it should be in integer..

  Sometimes Payment terms is not an integer.. how do I check and If it is not an integer how do I convert it into some default value

  Thanks..

• arun.sas

  SSChampion

  Points: 11831

  More actions

  July 31, 2009 at 4:28 am

  #1032843

  Hi,

  try this

  create table #temp1

  (

  slno int,

  inv_no varchar(10),

  inv_dt datetime,

  payment_term1 varchar(10)

  )

  insert into #temp1

  select 1,'INV01','2009-01-01','30'

  union all

  select 2,'INV02','2009-01-02','30'

  union all

  select 3,'INV03','2009-01-03','30'

  union all

  select 4,'INV04','2009-01-04','30'

  union all

  select 5,'INV05','2009-01-05','30'

  select *,(inv_dt+(cast(payment_term1 as int)))real_dt from #temp1

• c.vaibhav

  SSChasing Mays

  Points: 652

  More actions

  July 31, 2009 at 4:32 am

  #1032845

  thanks arun..

  but my payment terms has string value too..

  I can convert immediate, advance to 0 however it also contains values such as 30 pdc, I want to be able to extract 30 out of it..

  Thanks..

• c.vaibhav

  SSChasing Mays

  Points: 652

  More actions

  July 31, 2009 at 4:35 am

  #1032846

  okay.. thanks for all your help.. lets close this thread..

  i'll rather have a standard input for payment terms.. 30 PDC is too vague for computer to understand..

  I am finally using the following query..

  DateAdd("d", Convert(INT, Case When (Paymentterms like '%[a-z A-Z]%') then (0) else (PaymentTerms) end), InvoiceDate) as 'Due Date'

  Thanks..

  Vaibhav