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 1171
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: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |