SQL Timestamp Split

[krappleby025]

Hi can someone help me to do two things,

Firstly i have a timestamp in the following form

20021016005150

i need to split it into the following

Year = 2002
Month = 10
Date = 16
time = 005150

Now, any variable name will do, However the second part, i need is to add 1 month to it, and resave it to the database, in the same format...

so can someone please help, by providing the code, for that... just so i start with a variable datestamp of

20021016005150
and end up with a variable of
20021116005150

which i can then upload to the database

thanks

 

[krisbrixon]

Well, there is two ways to do this that I see:

1. is convert it to a real date and do the date adding function.

2. Treat it like a number and Add it yourself (see below)
This was written in VB.NET so you will have to "tweak" it to work in VBScript. (but you should understand what I am trying to do)

Private num1 As Long
Private numOrg As String = "20021016005150"
Private lFirst As Boolean = True

Private Function dude() As Long
Dim num2 As Long
If lFirst = True Then
num1 = CLng(numOrg)
lFirst = False
End If
num2 = CLng(Mid(CStr(num1),5))
If num1 > num2 Then
num1 = num1 - 1100000000 'Go from Dec to Jan
num1 = num1 + 10000000000 'Goto Next Year
Else
num1 = num1 + 100000000 'Add A Month
End If
Return num1
End Sub

Now, the problem would be it the database stored January like 20020116005150 cause this function will save it like 2002116005150. In that case (if the database can not understand it) you would have to use the Mid function some to get the format correct.

Kris
- Someone who thinks logically provides a nice contrast to the real world. - unknown

 

[Tarwn]

You could use the mid function to pull out the month and year, etc, increment, and replace them.

<%
Option Explicit

Dim temp, result
temp = &quot;20021016005150&quot;

Dim mYear, mMonth, mDayTime
mYear = mid(temp,1,4)
mMonth = cInt(mid(temp,5,2)) + 1
mDayTime = right(temp,8)

If cInt(mMonth) > 12 Then 
	mMonth = &quot;01&quot;
	mYear = cInt(mYear) + 1
ElseIf cInt(mMonth) < 10 Then
	mMonth = &quot;0&quot;&mMonth
End If

result = mYear & mMonth & mDayTime

response.Write temp&&quot;<br>&quot;
response.Write result&&quot;<br>&quot;
%>

This is somewhere between 1 and 2 above. I didn't actulally convert to a date, but I did break up the numbers before doing the math, hope it helps

-Tarwn --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
With enough resources, time, and coffee, anything is possible.