I am trying to import a text file into my database. One of the fields that I need is a Date/Time field in my database. My problem is that the value in the text file shows the date of 1/27/2015 15:32 as 0127151532. How do I convert this? I also need the 24 hour clock converted to a 12 hour clock at the same time.
You might try using Format to instead of Mid$. Maybe something like this?: - format("0127151532", "@@/@@/@@ @@@@")
7 1173
I have found a possible solution using the DateSerial() and TimeSerial() functions and then use the CDate() function to convert the strings to a date. The problem is that I have to do a lot of Mid() functions to split out the pairs of numbers. If anyone has a better solution, I would love to hear it.
Seth,
The trick is to look at each portion of the text string as a particular part of the date/time.
So, 0127151532 becomes..... - Left(strDateTime, 2) = ' the Month
-
Mid(strDateTime, 3, 2) = 'the day of the month
-
Mid(strDateTime, 5, 2) = 'the Year
Adding "/" between those values and you can use the CDate() Function to convert into a Date Value.
Likewise.... - Mid(strDateTime, 7, 2) = ' the Hour
-
Mid(strDateTime, 9, 2) = 'the Minute
And then, adding ":" between the text values and using the CDate() function will generate a Time.
Or, you could put all the values together before applying the CDate() function.
Hope this hepps!
You might try using Format to instead of Mid$. Maybe something like this?: - format("0127151532", "@@/@@/@@ @@@@")
Oh, and I think you can use Mod to convert to 12 hour format: - (1532 mod 1200) and (332 mod 1200) both return 332
Thank-you jforbes. That is exactly what I need. Here is my final code to get it to work the way that I need it to. - CDate(Format("0127151532", "@@/@@/@@ @@:@@"))
@jforbes,
Cool! I was not aware one could apply formats to strings using the Format() Function like that. Another tool I can put in my tool kit!
Glad it helps.
I was working on a custom formatting function recently that is somewhat related to this. The Format() will insert characters into a string, but I needed something to only put them in if they weren't there already. I made the following big o' mess to do just that. I though I would share incase you guys might want to use it in the future, plus I like to share =) - Public Function isCharAlphaNumeric(ByRef sString As String) As Boolean
-
isCharAlphaNumeric = (sString >= "A" And sString <= "Z") Or (sString >= "0" And sString <= "9")
-
End Function
-
-
Public Function formatPartNumber(ByRef sString As String, ByRef sTemplate As String) As String
-
-
' Use >, <, or ^ as the first character for
-
' > Uppercase
-
' < Lowercase
-
' ^ Camelcase (First letter of each word)
-
' Use ~ for any AlphaNumeric
-
' Use {} for an optional set where the start position is remembered and reset if a match doesn't occur
-
' Helpful for when there are multiple options at the end of a string
-
' Use [] for an optional set that doesn't reset the start postion
-
'
-
' Examples
-
' --------
-
' formatPartNumber("aBCd-efg/ap", ">~~~~[-~~~]{/AP}{/S}") -> ABCD-EFG/AP
-
' formatPartNumber("aBCdefg/sXYz", ">~~~~[-~~~]{/AP}{/S}[~][~][~]") -> ABCD-EFG/SXYZ
-
' formatPartNumber("aBCd-efg", "<~~~~[-~~~]{/AP}") -> abcd-efg
-
' formatPartNumber("aBCd-efgxyz", "^~~~~[-~~~]{/AP}") -> Abcd-efg
-
-
Dim sResult As String
-
Dim sBuild As String
-
Dim iTemplate As Integer
-
Dim iString As Integer
-
Dim iFormat As Integer
-
Dim sCurrentTemplateChar As String
-
Dim iOptionalStringPos As Integer
-
Dim bOptional As Boolean
-
Dim iOptionalMatch As Integer
-
Dim sTemp As String
-
-
iTemplate = 2
-
iString = 1
-
iFormat = 0
-
-
Select Case Left(sTemplate, 1)
-
Case ">" ' Upper
-
iFormat = 1
-
Case "<" ' Lower
-
iFormat = 2
-
Case "^" ' Camel Case
-
iFormat = 3
-
Case Else ' None
-
iFormat = 0
-
iTemplate = 1
-
End Select
-
-
Do While iTemplate <= Len(sTemplate)
-
sCurrentTemplateChar = Mid(sTemplate, iTemplate, 1)
-
Select Case sCurrentTemplateChar
-
Case "~" ' Character
-
sTemp = Mid(sString, iString, 1)
-
sBuild = sBuild & Mid(sString, iString, 1)
-
iString = iString + 1
-
If iOptionalMatch > 0 Then If Not isCharAlphaNumeric(sTemp) Then iOptionalMatch = 0
-
Case "{" ' Optional Match Start
-
sResult = sResult & sBuild
-
sBuild = ""
-
bOptional = True
-
iOptionalMatch = 1
-
Case "}" ' Optional Match End
-
If iOptionalMatch > 0 Then
-
sResult = sResult & sBuild
-
Else
-
iString = iOptionalStringPos ' Backup position
-
End If
-
sBuild = ""
-
iOptionalMatch = 0
-
Case "[" ' Optional Group Start
-
sResult = sResult & sBuild
-
sBuild = ""
-
bOptional = True
-
iOptionalMatch = 2
-
Case "]" ' Optional Group End
-
If iOptionalMatch <> 0 Then sResult = sResult & sBuild
-
sBuild = ""
-
bOptional = False
-
iOptionalMatch = 0
-
Case Else ' Other
-
If bOptional Then
-
Select Case iOptionalMatch
-
Case 0
-
Case 1
-
If Mid(sString, iString, 1) <> sCurrentTemplateChar Then
-
iOptionalMatch = 0
-
Else
-
iString = iString + 1
-
End If
-
Case 2
-
sTemp = Mid(sString, iString, 1)
-
If Len(sString) < iString Then iOptionalMatch = 0
-
If sCurrentTemplateChar = "~" Or sTemp = sCurrentTemplateChar Then
-
iString = iString + 1
-
End If
-
End Select
-
Else
-
If Mid(sString, iString, 1) = sCurrentTemplateChar Then iString = iString + 1
-
End If
-
sBuild = sBuild & sCurrentTemplateChar
-
End Select
-
If Not bOptional Then iOptionalStringPos = iString
-
iTemplate = iTemplate + 1
-
Loop
-
-
' Select case, haha, get it
-
Select Case iFormat
-
Case 1
-
formatPartNumber = UCase(sResult)
-
Case 2
-
formatPartNumber = LCase(sResult)
-
Case 3
-
formatPartNumber = StrConv(sResult, 3)
-
Case Else
-
formatPartNumber = sResult
-
End Select
-
End Function
-
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Nimmy |
last post by:
Hi,
I have a file which has different dates, I want to scanf them as CHAR and
convert them to DATE format, how can I do this?
Thanks
|
by: Convert TextBox.Text to Int32 Problem |
last post by:
Need a little help here. I saw some related posts, so here goes... I have some textboxes which are designed for the user to enter a integer value. In "old school C" we just used the atoi function...
|
by: Remi Caron |
last post by:
Hi,
I took over an Visual Object project (Visual Clipper) in that language there
is a function to: Convert a string containing a 32-bit binary date to a date
data type. That function is called...
|
by: Ken Varn |
last post by:
I have an unknown numeric Type object passed into a function. I want to run
a conversion on a string to convert the string to that Type object and
return an object of that type. Is there some way...
|
by: darrel |
last post by:
I have the following right now to enter a date into SQL getting the data
from some pull down menus:
-------------------------------------------------
dim dateCCJApprovedDate as DateTime
if...
|
by: abcabcabc |
last post by:
I write an application which can let user define own date format to input,
How to convert the date string to date value with end-user defined date
format?
Example, User Defined Date Format as...
|
by: John |
last post by:
I have a drop down that is showing dates, I need to pass the selected date
to my proc, so I pass it like this;
...
|
by: Aussie Rules |
last post by:
Hi,
I have a datepicker that show a calender. The user picks a date and the
time component is always 00:00.
I then have a drop down that provides a list of times, (10:00, 11:00 etc),
and I...
|
by: Mark Chimes |
last post by:
Hi All,
I need to search thru some strings and discard them if they canot be
converted to a decimal or interger value. What is the best way to do this?
cheers,
Mark Chimes
|
by: kronus |
last post by:
I'm receiving an xml file that has a child called modified and it represents a date value in the form of a string -- Nov 14, 2008 -- and in my app, I have items associated with each object and I'm...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |