Current month + 3 months.

Get help on databases - MySQL, Oracle, Access, etc.
Post Reply
DeeVeeDee
Registered User
Posts: 172
Joined: 09 Apr 2010, 15:18

Current month + 3 months.

Post by DeeVeeDee »

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 ?
_̴ı̴̴̡̡̡ ̡͌l̡̡̡ ̡͌l̡*̡̡ ̴̡ı̴̴̡ ̡̡͡|̲̲̲͡͡͡ ̲▫̲͡ ̲̲̲͡͡π̲̲͡͡ ̲̲͡▫̲̲͡͡ ̲|̡̡̡ ̡ ̴̡ı̴̡̡ ̡͌l̡̡̡
Kronos
Moderator Emeritus
Posts: 4280
Joined: 28 May 2003, 02:00
Location: Azeroth
Contact:

Re: Current month + 3 months.

Post by Kronos »

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)
Image
User avatar
rustypup
Registered User
Posts: 8872
Joined: 13 Dec 2004, 02:00
Location: nullus pixius demonica
Contact:

Re: Current month + 3 months.

Post by rustypup »

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'....
Most people would sooner die than think; in fact, they do so - Bertrand Russel
DeeVeeDee
Registered User
Posts: 172
Joined: 09 Apr 2010, 15:18

Re: Current month + 3 months.

Post by DeeVeeDee »

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.
_̴ı̴̴̡̡̡ ̡͌l̡̡̡ ̡͌l̡*̡̡ ̴̡ı̴̴̡ ̡̡͡|̲̲̲͡͡͡ ̲▫̲͡ ̲̲̲͡͡π̲̲͡͡ ̲̲͡▫̲̲͡͡ ̲|̡̡̡ ̡ ̴̡ı̴̡̡ ̡͌l̡̡̡
Post Reply