sql query involving date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sql query involving date

Author

Topic

c.vaibhav
Starting Member

26 Posts

Posted - 2009-07-31 : 05:47:24

Hi friends,

I have table with the following columns:

Invoice Date, Payment Terms, Invoice Number, .....
30th June 2009, 15, S000001,
30th June 2009, 30, S000001,
30th June 2009, Advance, S000001,
30th June 2009, Immediate, S000001,
30th June 2009, 30 PDC, S000001,

Now I want to write a select query with following columns,

Invoice Date, Due Date, Invoice Number, .....
30th June 2009, 15th July 2009, S000001,
30th June 2009, 30th July 2009, S000001,
30th June 2009, Advance, S000001,
30th June 2009, 30th June 2009, S000001,
30th June 2009, 30th July 2009, S000001,

Thanks in advance..

Regards,
Vaibhav

c.vaibhav
Starting Member

26 Posts

Posted - 2009-07-31 : 05:49:40

sorry.. it got disoriented..

I basically want to find out due date based on the invoice date and payment terms..
i.e. If the invoice date is 30th June and if the Payment terms is 30days then I want a select query that gives me the due date as 30th July..

thanks..

Vaibhav

c.vaibhav
Starting Member

26 Posts

Posted - 2009-07-31 : 06:17:00

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..

Sachin.Nand

2937 Posts

Posted - 2009-07-31 : 06:26:53

quote:
Originally posted by c.vaibhav

okay.. i got the following query..

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..

where Paymentterms not like '%[a-z A-Z]'

PBUH

c.vaibhav
Starting Member

26 Posts

Posted - 2009-07-31 : 06:29:21

great.. thanks..

if suppose my payment terms has something like '30 pdc' then can I extract 30 out of it?

c.vaibhav
Starting Member

26 Posts

Posted - 2009-07-31 : 06:35:29

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

Sachin.Nand

2937 Posts

Posted - 2009-07-31 : 07:20:17

quote:
Originally posted by c.vaibhav

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

You are welcome.
But the query will work provided u dont have any special character in paymentterms

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources