[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Converting a String of Numbers to Date in a function

Posted on 2004-09-21
12
Medium Priority
?
228 Views
Last Modified: 2010-04-23
Hi there,

I have a field in my Access database that is type text, and I need to convert it to type date programatically within the function as I have 17,000 fields and changing each field individually is not an option.

The text in the database looks like 12312003, and I would like to change it on the webpage to date type 12/31/2003



My function looks like this (var1 is the string to convert):

Function CHECKLMPDATE(var1 as object) as String
Return (format(var1, "MM/DD/YY")).ToString
End Function


The code above gives me errors. What is the correct syntax??

Kittrick
0
Comment
Question by:Kittrick
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +3
12 Comments
 
LVL 2

Expert Comment

by:TeddyZero
ID: 12116209
Try this...

Function CHECKLMPDATE(var1 as object) as String
    Return New Date(CInt(var1.Substring(4, 4)), CInt(var1.Substring(0, 2)), CInt(var1.Substring(2, 2))).ToString("MM/dd/yy")
End Function
0
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 12116352
Try this...

Function CHECKLMPDATE(var1 as String) as String
   If (var1 & DbNull.Value) <> "" Then
         var1 = Ctype(var1, Date).ToShortDateString()
         Return var1
   Else
         Return ""
   End If
End Function
0
 
LVL 18

Accepted Solution

by:
DotNetLover_Baan earned 2000 total points
ID: 12116395
oops... missed that line..
        var1 = var1.Substring(0, 2) & "/" & var1.Substring(2, 2) & "/" & var1.Substring(4)

Whole function:

Function CHECKLMPDATE(var1 as String) as String
   If (var1 & DbNull.Value) <> "" Then
         var1 = var1.Substring(0, 2) & "/" & var1.Substring(2, 2) & "/" & var1.Substring(4)
         var1 = Ctype(var1, Date).ToShortDateString()
         Return var1
   Else
         Return ""
   End If
End Function

-Baan
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 12116411
Make sure to check for NULL.. as I showed.
0
 
LVL 7

Expert Comment

by:natloz
ID: 12116454
Dim mnth as string
Dim dy as string
Dim yr as string
Dim dob as string

mnth = Mid(dob, 0, 2)
dy = Mid(dob, 2, 2)
yr = Mid(dob, 4, 4)
dob = dy & "/" & mnth & "/" & yr

mdob = CDate(dob) <---Convert to Date
0
 
LVL 4

Author Comment

by:Kittrick
ID: 12116716
Ok DotNetLover_Baan,

Your code works but I still have an error. When the people in our office input data, it's done in the order mm/dd/yyyy, but as you know from the example it's done in a text field. When looking at incomplete documentation, sometimes it is missing the day or the month. If it is, they will enter 99 in iplace of the missing data.

For instance, if we know the month, but not the day, we will enter 12992004 which would convert to 12/99/2004, but the error is coming up because it's not a real date.

How could I get it to output the number 99 (instead of the date) if either the month or the date is the number 99??

Kittrick
0
 
LVL 2

Expert Comment

by:TeddyZero
ID: 12116765
Function CHECKLMPDATE(var1 as object) as String
    Return String.Concat(var1.Substring(0, 2), "/",var1.Substring(2, 2), "/", var1.Substring(4, 4))
End Function
0
 
LVL 7

Expert Comment

by:natloz
ID: 12116786
That would be an invalid Date Type....unless you convert it to a STRING and not a DateType...

You could do...

Dim mnth as string
Dim dy as string
Dim yr as string
Dim dob as string = "12992004"

mnth = Mid(dob, 0, 2)
dy = Mid(dob, 2, 2)
yr = Mid(dob, 4, 4)
dob = mnth & "/" & dy & "/" & yr
0
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 12116803
You can not show 99 or 9999 in a date variable. How are you planning to show it on the web page, if 99 or 9999 comes ?
The only way out I see, is to change the data type from DATE to STRING in the webpage, and since in the database this field is STRING, you don't even have to change it to Date format.
Just use:
Function CHECKLMPDATE(var1 as String) as String
   If (var1 & DbNull.Value) <> "" Then
         var1 = var1.Substring(0, 2) & "/" & var1.Substring(2, 2) & "/" & var1.Substring(4)
         Return var1
   Else
         Return ""
   End If
*** In this case you HAVE to keep that field as STRING in the page.
0
 
LVL 12

Expert Comment

by:fulscher
ID: 12118062
... or, set it to 01 - which is wrong, but valid;

... or, store the individual fields separately and combine them on the fly to a text format. If a field is unknown, just leave it empty and in the output set it to "XX"

However, if you want to process dates (with sorting etc.), you could also modify the format to YYYYMMDD. If a part is unkown, set it to 99 or XX. Store them as strings. This way, you can at least sort for them without messing up the results (the MMDDYYYY format will not be sortable if stored as string).

HTH, Jan
0
 
LVL 2

Expert Comment

by:ahmadfuwad
ID: 12120480
'function for converting string into date
Function convertindate(ByVal s As String) As DateTime
        s = s.Chars(0) + s.Chars(1) + "/" + s.Chars(2) + s.Chars(3) + "/" + s.Chars(4) +
 s.Chars(5) + s.Chars(6) + s.Chars(7)
        Dim d As DateTime
        d = CDate(s)
        Return d
    End Function

'calling of function
   Dim d As DateTime
        d = convertindate(12232004)
        MsgBox(d)
0
 
LVL 12

Expert Comment

by:fulscher
ID: 12120542
... addition to my previous post: "MMDDYYYY format will not ne sortable" - of course you can sort using this format, but the results are not sorted by Year, Month and Day but by Month, Year and Day. Usually, you do not want this. So, it may be better to save a string in the YYYYMMDD format which sorts correctly and, if a day is unknown, will still sort correctly.

Jan
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question