PCFormat Forum Archive

Posted: **12 Apr 2010, 12:22**

I have a Stored Proc SQL2005 which generates and Order to an XML file directly to Filesystem. which then gets imported into another using its own import service

The dates are stored IE '20100331' in the source DB.

The destination DB dates are formatted as '2010-03-31'

One of the dates that gets imported in to the Destination DB is the current date + 3 months.

This is how i convert from '20100331' to '2010-03-31' + 3 months.

Code: Select all

substring(convert(varchar(10),rsOrder.ORDDATE),1,4) + '-' + substring(convert(varchar(10),rsOrder.ORDDATE + 300 ),5,2 )   + '-' + substring(convert(varchar(12),rsOrder.ORDDATE),7,2)   as ORD_EXP_DATE,

Formating the other dates works perfectly with the above statement without the +300 the + 300 add 3 to the current month.
Now if a date is 2009-11-10 it will result in 2009-14-10.

What do i do ?

Posted: **12 Apr 2010, 12:58**

Can I assume that "SQL2005" means Microsoft SQL 2005?
if so, use the DATEADD() function to add months to your date.

Code: Select all

DATEADD(month, 3, rsOrder.ORDDATE)

Posted: **12 Apr 2010, 13:22**

first prize would be to stop storing dates as string literals...

second prize would be to ponder the viability of dates like '2010-02-31'....

Posted: **12 Apr 2010, 13:37**

Thanks Kronos, will give that a go.

rustypup wrote:first prize would be to stop storing dates as string literals...

second prize would be to ponder the viability of dates like '2010-02-31'....

This is not a configurable setting. Its just how it is.

PCFormat Forum Archive

Current month + 3 months.

Current month + 3 months.

Re: Current month + 3 months.

Re: Current month + 3 months.

Re: Current month + 3 months.