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

 

[vladk]

What I can see, your code does not work (sometimes) when the day can be the month (<=12).

 

[Hypetia]

When you store a date in a string as xx/yy/zzzz, VB always try to evaluate the xx as month and yy as day because this behaviour is built into VB. If this does not make a valid date, VB swaps xx and yy and tries again.

In your case, when you format "20050425" to "25/04/2005" and convert it to a Date variable, VB tries to recognize 25 as Month number, which is not valid. Then VB recognizes 25 as day and 04 as month and makes a valid date as you wanted.

On the other hand, "20050411" is formatted to "11/04/2005" and successfully recognized in first attempt as Nov 4, 2005, which does not make sense to you.

To address this problem, never build your dates using xx/yy/zzzz strings. Instead, always use the DateSerial function, which accepts Day, Month and Year as arguments and makes a valid date without any confusion.

Following code shows how build date variables using the DateSerial function.
___
[tt]
Private Sub Form_Load()
'make sure you declare NewDate as a Date variable
Dim NewDate As Date
Dim F1 As String

F1 = "'20050411'"
NewDate = DateSerial( _
CInt(Mid$(F1, 2, 4)), _
CInt(Mid$(F1, 6, 2)), _
CInt(Mid$(F1, 8, 2)))
MsgBox Format$(NewDate, "mmm d, yyyy")


F1 = "'20050425'"
NewDate = DateSerial( _
CInt(Mid$(F1, 2, 4)), _
CInt(Mid$(F1, 6, 2)), _
CInt(Mid$(F1, 8, 2)))
MsgBox Format$(NewDate, "mmm d, yyyy")

End Sub[/tt]

 

[strongm]

>When you store a date in a string as xx/yy/zzzz, VB always try to evaluate the xx as month and yy as day

Depends on your regional settings

 

[Hypetia]

Sorry, I meant this about Date literals.

Considering the following code.
___
[tt]
Private Sub Form_Load()
Dim D As Date
D = #6/3/2005#
Debug.Print FormatDateTime(D, vbLongDate)
End Sub[/tt]
___

With regional settings set to English (US) I get this.
[tt]Friday, June 03, 2005[/tt]

With regional settings set to English (UK) I get this.
[tt]03 June 2005[/tt]

The short-date formats for these regions are m/d/yyyy and dd/mm/yyyy respectively. But the date literals are always interpreted in EN-US (m/d/yyyy) format.

 

[strongm]

Absolutely.

 

[tsuji]

May I say, until another plausible interpretation prevail.
[tt]
Dim D As Date
D = #[blue]2[/blue]6/3/2005#
Debug.Print FormatDateTime(D, vbLongDate)
[/tt]
It can still be quite confusing.

 

[tsuji]

May be not in IDE after all!

 

[toon10]

Thanks for all the pointers guys.

It makes a little more sense now because some dates are fine and some are not. Normally I would have expected to see either all the dates the wrong way around or all of them the correct way around.

I'll try the above.

Cheers
Andrew

 

[toon10]

Bugger, still the same using the following code:

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

I must be missing something?

 

[strongm]

Lose the #

 

[toon10]

OK, I've lost the "#"

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

If I don't include the hash here or on my "insert into" line then I get a type mistmatch (i.e., it doesn't know its a date)so I put the #'s on the INSERT which leaves me back at square 1, the 11/04/2005 is showing up as 04/11/2005.

 

[RoyVidar]

So - you're just reading it from a text file, feeding it to an SQL string, then append?

There shouldn't need to be any need to use any date conversion, just prepare the string a little (assuming the variable f2 is a string variable):

[tt]f2 = "#" & Mid$(F1, 2, 4) & "-" & Mid$(F1, 6, 2) & "-" & Mid$(F1, 8, 2) & "#"[/tt]

Else, the usual would be to format the date when assinging to the sql string:

[tt]...set datefield = #" & format(date,"yyyy-mm-dd") & "#...[/tt]

Roy-Vidar

 

[toon10]

I had something similar before using "/" instead of "-". Still makes no odds though. First one I checked is saying that 06/09/2004 is 9th June 2004!

 

[RoyVidar]

First one I checked is saying that 06/09/2004 is 9th June 2004!" - yup, that's correct. You may want to read through this thread again, as this is explained previously, see for instance Hypetia's first reply.

- but when did 06/09/2004 come into the pictue - I mean both the format and the date?

Are saying that if you take a string in the following format

'20050512'

Then add it to an SQL string with

...somedatefield = #" & Mid$(F1, 2, 4) & "-" & Mid$(F1, 6, 2) & "-" & Mid$(F1, 8, 2) & "#..."

and getting wrong dates??? (this gives 12. May or 5. Dec?)

Roy-Vidar

 

[toon10]

First one I checked is saying that 06/09/2004 is 9th June 2004!" - yup, that's correct. [In America maybe!]

I know it's been explained in this thread but I've tried all the solutions posted and the problem remains.

My string is in this format "20050512" which needs to be converted to 12th May 2005 albiet in the format 12/05/2005.

I'll post my code again to show you what I'm getting:

(These are three seperate bits of code but all come back with the same result) f1 is the date string and i want f2 to be the converted date.

f2 = DateSerial(CInt(Mid$(F1, 2, 4)), CInt(Mid$(F1, 6, 2)), CInt(Mid$(F1, 8, 2)))

f2 = "#" & CDate(Mid(F1, 8, 2) & "/" & Mid(F1, 6, 2) & "/" & Mid(F1, 2, 4)) & "#"

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

Alternatively, I've dropped the "#" and just included that on the SQL insert into stage but nothing seems to do it.

 

[RoyVidar]

Yes, seeing the code might help, please do so, and please also include declarations of the variables.

Roy-Vidar

 

[toon10]

Here's the whole insert loop...

Open ImportDataFile For Input As 1 'import selected text file
While Not EOF(1)
Line Input #1, strTemp
ImportString = ImportString & strTemp 'ImportString is now a valid record
ImportString = Replace(strTemp, "'", "''") 'counteracts problems with apostrophe's in peoples names
FItemArray = Split(ImportString, ",")
F1 = "'" & FItemArray(0) & "'"
f2 = DateSerial(CInt(Mid$(F1, 2, 4)), CInt(Mid$(F1, 6, 2)), CInt(Mid$(F1, 8, 2)))
f3 = "'" & FItemArray(1) & "'"
f4 = "'" & FItemArray(2) & "'"
f5 = "'" & FItemArray(3) & "'"
f6 = "'" & FItemArray(4) & "'"
f7 = "'" & Mid(f6, 2, 2) & ":" & Mid(f6, 4, 2) & ":" & Mid(f6, 6, 2) & "'" 'time after conversion
f8 = "'" & FItemArray(5) & "'"
f9 = "'" & FItemArray(6) & "'"
f10 = "'" & FItemArray(7) & "'"
f11 = "'" & FItemArray(8) & "'"

If varCounter = 0 Then
'do nothing as the header record layer has just been read
Else 'insert fields into database
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 (#" & f2 & "#, " & f3 & ", " & f4 & ", " & f5 & ", " & f7 & ", " & f8 & ", " & f9 & ", " & f10 & ", " & f11 & ");"
'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 (" & varDate & ", " & f3 & ", " & f4 & ", " & f5 & ", " & f7 & ", " & f8 & ", " & f9 & ", " & f10 & ", " & f11 & ");"
End If

varCounter = varCounter + 1 'increment counter so that all non header records will be inserted
ImportString = "" 'reset for next pass to read in next record
Wend

I haven't declared any of the F variables (makes no odds)

 

[RoyVidar]

Now, look at my above...

[tt]dim f2 as string
f2 = "#" & Mid$(F1, 2, 4) & "-" & Mid$(F1, 6, 2) & "-" & Mid$(F1, 8, 2) & "#"
..
... VALUES (" & f2 & ", "...[/tt]

Or, as also from above

[tt]dim f2 as date
f2 = DateSerial(CInt(Mid$(F1, 2, 4)), CInt(Mid$(F1, 6, 2)), CInt(Mid$(F1, 8, 2)))
..
... VALUES (#" & format$(f2, "yyyy-mm-dd") & "#, "...[/tt]

Roy-Vidar

 

[toon10]

I'll try it, I didn't get what you meant with the VALUES (#" & format$(f2, "yyyy-mm-dd") & "#, "... part but I see you put that on the insert statement.