VB Date functions into ISO format for SQL server

[newedgemedia.co.uk]

Hi everyone,

I am developing a advertising portal site for villa rentals. In which we
require to charge for advertising on a 6 or 12 month basis. Each advert has an
advert end date, which is stored in an SQL database as a shortdatetime, with a
short date format.

Someone can add 6 months or 12 to their advert even when the advert end date
has passed. This part of the coding is fine, no problem. It is however when
someone pays for the advert in advance which cause a problem. As the advert end
date needs to be updated by 6 or 12 months from the advert end date in the
database.

Now I have all sort of problems with the date function, and therefore have
converted everything into ISO format. When i need to do is read the date into
the script, convert it to ISO, add the period to it (i.e. 6 or 12 months) then
put it into a form hidden field. this is the code thus far....

------------------------
'get new end date
varAdvertEndDate = (rsProp.Fields.Item("AdvertEndDate").Value)
varPeriod = request.querystring("period")
if varAdvertEndDate < Date() then
if varPeriod = 12 then
varDay = Day(date)
varMonth = Month(date)
varYear = Year(date) + 1
varNewEndDate = varYear & _
"-" & Right(Cstr(varMonth + 100),2) & _
"-" & Right(Cstr(varDay + 100),2)
end if
if varPeriod = 6 then
varDay = Day(date)
varMonth = Month(date)
if varMonth = 7 then varMonth = 1
if varMonth = 8 then varMonth = 2
if varMonth = 9 then varMonth = 3
If varMonth = 10 then varMonth = 4
if varMonth = 11 then varMonth = 5
if varMonth = 12 then varMonth = 6
if varMonth <> 7 OR varMonth <> 8 OR varMonth <> 9 OR varMonth <> 10 OR
varMontn <> 11 OR varMontn <> 12 then varMonth = varMonth + 6
varYear = Year(date)
varNewEndDate = varYear & _
"-" & Right(Cstr(varMonth + 100),2) & _
"-" & Right(Cstr(varDay + 100),2)
end if
b]else
' this is the problem
varNewEndDate = dateAdd("m", varPeriod, varAdvertEndDate)
end if

-------------------------------------

However this dateadd function turns the date into a local format rather than
the ISO YYYY-MM-DD which i require it to be.

Has anoyone got any ideas how to tackle this??

many thanks

loz howlett

[CMBergin]

SQL Server is perfectly capable of handling the date manipulation on its
own. That way, you don't have to worry about formatting since there's only
one server involved and no dates are passed around as strings.
Sample procedure to add any number of months to an ad:

CREATE PROCEDURE [ExtendAd] (
@AdID INT,
@NumMonths INT
)
AS
DECLARE @CurrentDate SMALLDATETIME
DECLARE @EndDate SMALLDATETIME
SELECT @EndDate = AdvertEndDate FROM AdTable WHERE AdID=@AdID

SET @CurrentDate = CAST(LEFT(CONVERT(VARCHAR,GETDATE(),120),10) AS
SMALLDATETIME) --all this cast/convert business just chops the time off of
the current date/time stamp
IF @EndDate < @CurrentDate
SET @EndDate = @CurrentDate

UPDATE AdTable
SET AdvertEndDate = DATEADD(m,@NumMonths,@EndDate)
WHERE AdID=@AdID
GO

"newedgemedia.co.uk" <webforumsuser@macromedia.com> wrote in message
news:cvsmu5$dgs$1@forums.macromedia.com...

> Hi everyone,
>
> I am developing a advertising portal site for villa rentals. In which we
> require to charge for advertising on a 6 or 12 month basis. Each advert

has an

> advert end date, which is stored in an SQL database as a shortdatetime,

with a

> short date format.
>
> Someone can add 6 months or 12 to their advert even when the advert end

date

> has passed. This part of the coding is fine, no problem. It is however

when

> someone pays for the advert in advance which cause a problem. As the

advert end

> date needs to be updated by 6 or 12 months from the advert end date in the
> database.
>
> Now I have all sort of problems with the date function, and therefore

have

> converted everything into ISO format. When i need to do is read the date

into

> the script, convert it to ISO, add the period to it (i.e. 6 or 12 months)

then

> put it into a form hidden field. this is the code thus far....
>
> ------------------------
> 'get new end date
> varAdvertEndDate = (rsProp.Fields.Item("AdvertEndDate").Value)
> varPeriod = request.querystring("period")
> if varAdvertEndDate < Date() then
> if varPeriod = 12 then
> varDay = Day(date)
> varMonth = Month(date)
> varYear = Year(date) + 1
> varNewEndDate = varYear & _
> "-" & Right(Cstr(varMonth + 100),2) & _
> "-" & Right(Cstr(varDay + 100),2)
> end if
> if varPeriod = 6 then
> varDay = Day(date)
> varMonth = Month(date)
> if varMonth = 7 then varMonth = 1
> if varMonth = 8 then varMonth = 2
> if varMonth = 9 then varMonth = 3
> If varMonth = 10 then varMonth = 4
> if varMonth = 11 then varMonth = 5
> if varMonth = 12 then varMonth = 6
> if varMonth <> 7 OR varMonth <> 8 OR varMonth <> 9 OR varMonth <> 10 OR
> varMontn <> 11 OR varMontn <> 12 then varMonth = varMonth + 6
> varYear = Year(date)
> varNewEndDate = varYear & _
> "-" & Right(Cstr(varMonth + 100),2) & _
> "-" & Right(Cstr(varDay + 100),2)
> end if
> b]else
> ' this is the problem
> varNewEndDate = dateAdd("m", varPeriod, varAdvertEndDate)
> end if
>
> -------------------------------------
>
> However this dateadd function turns the date into a local format rather

than

> the ISO YYYY-MM-DD which i require it to be.
>
> Has anoyone got any ideas how to tackle this??
>
> many thanks
>
> loz howlett
>