VB Date functions into ISO format for SQL server

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 2000 Forums

SQL Server Development (2000)

VB Date functions into ISO format for SQL server

Author

Topic

lozhowlett
Starting Member

3 Posts

Posted - 2005-02-27 : 12:07:26

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

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-02-27 : 12:15:57

The ONLY line of code you need is this:

varNewEndDate = dateAdd("m", varPeriod, varAdvertEndDate)

All of the other date fiddling is unnecessary. Once you have the correct value, then you can do:

dateValue=year(varNewEndDate) & "-" & right("0" & month(varNewEndDate),2) & "-" & right("0" & day(varNewEndDate),2)

lozhowlett
Starting Member

3 Posts

Posted - 2005-02-27 : 12:33:25

it doesnt work tho... i get a smalldatetime error...

when coverting the string smalldatetime the function ended in a out of range value (smalldatetime)

that why i needed to force this into ISO YYYY-MM-DD otherwise it simply wont input the value into the databaase. Dont knoe ehy, but simply wont!

lozhowlett
Starting Member

3 Posts

Posted - 2005-02-27 : 12:39:23

ohh sorted now.. thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources