CDate function only works on half my data!

[toon10]

I have a comma separated text file which stores dates from SAP in the following format “YYYYMMDD”. I read in the file, convert the date to a proper format ready for writing to a table. The problem is that the following code works perfectly for half of the data but for some reason, some of the dates it reads in, it puts the month and days the wrong way around so when I use my DateDiff function, only half of the days differences are correct! I can’t see what I’m doing wrong. I’ve used this method in the past with total success!

Below is an example of some of the dates reading in fine and some getting jumbled up.

Converted Table from VB Original text file

ID Start Date End Date Difference Correct Original Start Date
010640017403| 04/11/2005 |14/04/2005 |-204 N 20050411
010640017405| 25/04/2005 |26/04/2005 |1 Y 20050425
010640017409| 19/04/2005 |20/04/2005 |1 Y 20050419
010640017412| 27/04/2005 |27/04/2005 |0 Y 20050427
010640017421| 03/10/2005 |10/05/2005 |-146 N 20050310
010640017430| 04/06/2005 |06/04/2005 |-59 N 20050406
010640017434| 04/05/2005 |11/05/2005 |7 Y 20050405


My code to convert the original into a proper date:

(F1 is the original start date with ‘marks around it, i.e. ‘20050512’)

NewDate = "#" & CDate(Mid(f1, 8, 2) & "/" & Mid(f1, 6, 2) & "/" & Mid(f1, 2, 4)) & "#"

My DateDiff function:
varDaysDiff = DateDiff("D", rs.Fields(2), rs.Fields(4))

(where rs.Fields(2) is the start date I’m using from the text file and rs.Fields(4) is the end date I’m getting from elsewhere.)

Any ideas?

Thanks
Andrew

 

[strongm]

Wait a minute, wait a minute ...

You may need to manipulate 20050512 to get it to appear as you want it to in VB, but you shouldn't need to touch it all to insert it into a SQL table (if the column is DateTime or SmallDateTime)

Now, I still don't understand how it is that you basic loop (formatting for display) is getting half the dates wrong as:

[blue]    Dim FItemArray() As String
    Dim f1 As String
    Dim f2 As Date
    Dim lp As Long
    
    'Just used to build the necessary array for example
    FItemArray = Split("20050411 20050425 20050419 20050427 20050310 20050406 20050405", " ")
    
    For lp = LBound(FItemArray) To UBound(FItemArray)
        f1 = "'" & FItemArray(lp) & "'"
        f2 = DateSerial(CInt(Mid$(f1, 2, 4)), CInt(Mid$(f1, 6, 2)), CInt(Mid$(f1, 8, 2)))
        ' lists dates [b]in current regional settings shortdate format[/b]
        MsgBox f2
    Next[/blue]

works fine for me in returning the correct dates in the format defined by my regional settings.

And the following inseerts the correct dates in both the test colums I created in my test SQL Server database (albeit using ADO rather than DAO)

[blue]    Dim FItemArray() As String
    Dim f1 As String
    Dim f2 As Date
    Dim lp As Long
    
    Dim myCon As connection
    Dim myRS As Recordset
    Dim myCOmmand As Command
    
    Set myCon = New connection
    myCon.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=<yourid>;Initial Catalog=<yourServer>;Data Source=<yourDB>"
    
    
    myCon.Open
    Set myCOmmand = New Command
    Set myCOmmand.ActiveConnection = myCon
    
    FItemArray = Split("20050411 20050425 20050419 20050427 20050310 20050406 20050405", " ")
    For lp = LBound(FItemArray) To UBound(FItemArray)
    
        f1 = "'" & FItemArray(lp) & "'"
        f2 = DateSerial(CInt(Mid$(f1, 2, 4)), CInt(Mid$(f1, 6, 2)), CInt(Mid$(f1, 8, 2)))
    
        
        ' testdates table has two columns - Serialdate defined as Char and RealDate defines as DateTime
        myCOmmand.CommandText = "insert into testDates ([SerialDate],[RealDate]) VALUES ('" & f2 & "', " & f1 & ")"
        
        Set myRS = myCOmmand.Execute
    
    Next[/blue]

 

[toon10]

Me niether! I've been working on this all day and it still gives me the wrong answers. I'm using ADO and the date field in my table is set to "date".

I've done very similar things to this in the past without any problems, I just don't understand what I have to do! There's nothing on MSDN or other forumns that I can find where this has cropped up.

Here's the important bits as it stands...

Dim f2 As String

F1 = "'" & FItemArray(0) & "'"
f2 = DateSerial(CInt(Mid$(F1, 2, 4)), CInt(Mid$(F1, 6, 2)), CInt(Mid$(F1, 8, 2)))

DB.Execute " INSERT INTO tblRepairs ([Log Date],[Activity Code],[Notification No],[User Name],[Time],[Customer Name],[Business Unit],[Last Activity Description],[Last Activity Code]) VALUES (#" & Format$(f2, "dd-mm-yyyy") & "#, " & f3 & ", " & f4 & ", " & f5 & ", " & f7 & ", " & f8 & ", " & f9 & ", " & f10 & ", " & f11 & ");"

I'm giving myself 30 minutes before my head explodes.

Cheers
Andrew

 

[Hypetia]

In your above code, try making the following changes.
___
[tt]
Dim f2 As Date

F1 = "'" & FItemArray(0) & "'"
f2 = DateSerial(CInt(Mid$(F1, 2, 4)), CInt(Mid$(F1, 6, 2)), CInt(Mid$(F1, 8, 2)))

DB.Execute " INSERT INTO tblRepairs ([Log Date],[Activity Code],[Notification No],[User Name],[Time],[Customer Name],[Business Unit],[Last Activity Description],[Last Activity Code]) VALUES (#" & Format$(f2, "m/d/yyyy") & "#, " & f3 & ", " & f4 & ", " & f5 & ", " & f7 & ", " & f8 & ", " & f9 & ", " & f10 & ", " & f11 & ");"[/tt]

 

[strongm]

Did you read Hypetia's "When you store a date in a [date literal] as xx/yy/zzzz, VB always try to evaluate the xx as month and yy as day because this behaviour is built into VB"?

 

[toon10]

yeah, i did. i no why it's doing it but i still don't know how to solve it!

i've done exectly the same thing before with the only difference being the format of the original date and never had an issue.

cheers

 

[strongm]

Right - when you do this:

"#" & Format$(f2, "dd-mm-yyyy") & "#"

you are building a date literal from f2, which is then interpreted by VB/Access, as per all the descriptions and caveats that you have been given in this thread, as mmddyyyy rather than the dd-mm-yyyy that you think it is

 

[toon10]

yeah, thanks people. i think i've got it sussed.

i've applied the format$ method to every insert that has dates in my app and it seems to be storing the dates correctly after seemingly not doing that yesterday.

cheers for all the help
Andrew