Converting Data Types Question

# Thread: Converting Data Types Question

1. Senior Member
Join Date
Dec 1969
Posts
1,407

## Converting Data Types Question

i have a field in a table that holds a number that is representative of a date.<BR><BR>ex.<BR><BR>ElectionID<BR>200401<BR>20 0402<BR>200403<BR>200404<BR><BR>and so on and so forth.<BR><BR>how can i take the number, 200404, and separate it into 2004 and 04?<BR><BR>thanks.

2. Senior Member
Join Date
Dec 1969
Posts
972

## RE: Converting Data Types Question

Sure, just convert it to a string:<BR><BR>strNumber = CStr(200404)<BR><BR>And then split it up using Left, Mid and/or Right:<BR><BR>strYear = Left(strNumber, 4)<BR>strMonth = Right(strNumber, 2)<BR><BR>To convert those back to integers you can use CInt:<BR><BR>intYear = CInt(strYear)<BR>intMonth = CInt(strMonth)<BR><BR><BR>Alternatively, you could do some calculations:<BR><BR>lngNumber = 200404<BR>intYear = Int(lngNumber / 100)<BR>intMonth = lngNumber - 100 * intYear

3. Senior Member
Join Date
Dec 1969
Posts
11,247

## RE: Converting Data Types Question

Access<BR><BR>Left(electionID, 1, 4) As ElectionYear, Mid(electionID, 5, 2) as Electionmonth<BR><BR>SQL Server<BR><BR>Substring(electionID, 1, 4) As ElectionYear, Substring(electionID, 5, 2) as Electionmonth

4. Senior Member
Join Date
Dec 1969
Posts
3,195

## The "quick" answer is to

treat as a "string" and use string functions to separate the different parts. Both SQL(substr,etc.) and Vbscript(mid,etc.) have string functions available to you.<BR><BR>The better answer is to probably store that field as a date/time field instead of a number. Then you could use built-in date functions to retrieve just the part of the date you wwant(year, month,etc.). Plus you have the benefits of storing the field as a date.

5. Senior Member
Join Date
Dec 1969
Posts
11,247

And the other answer is better

6. Senior Member
Join Date
Dec 1969
Posts
1,407

## RE: Converting Data Types Question

Error Type:<BR>Microsoft VBScript runtime (0x800A0006)<BR>Overflow: &#039;CInt&#039;<BR>/EmpOfMonth/Choose.asp, line 15<BR><BR>my code:<BR><BR>stringdate = objRS("ElectionID")<BR>stringdate = CStr(stringdate)<BR>yr = Year(Left(stringdate,4))<BR>mo = Month(Right(stringdate,2))<BR>stringdate = CInt(stringdate) &#060;--------------line 15<BR><BR>any ideas? thanks.

7. Senior Member
Join Date
Dec 1969
Posts
11,247

## Clng <nt>

.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•