Access MDB and dates 1

[ADoozer]

i know this has been covered a lot, but i cant get any of the solutions ive tried to work.

i have a type

Public Type HandData
    g_HandID As Long
    g_HandDate As Date
    g_WasSeated As Boolean
    g_XMLDump As String
End Type

Public Hands() As HandData

i read values from a SQLite database (1 table, 4 columns)

the date comes from the SQLite DB in the form of

"20050829153629"

now, i need to format this string and save it to an Access DB. i believe that Access is a bit awkward and requires american mm/dd/yyyy format, so i format my string for that.

Hands(iCnt).g_HandDate=Mid(rs.ColumnValue(1), 7, 2) & "/" & Mid(rs.ColumnValue(1), 5, 2) & _
"/" & Mid(rs.ColumnValue(1), 1, 4) & " " & Mid(rs.ColumnValue(1), 9, 2) & _
":" & Mid(rs.ColumnValue(1), 11, 2) & ":" & Mid(rs.ColumnValue(1), 13, 2)

however when i look at the Date in the watches window, the string is turned into the uk format dd/mm/yyyy.

#29/08/2005 15:36:29#

i store the date into accesss using the folowing SQL string

sqlStr="INSERT INTO HandData (HandID,HandDate,WasSeated) VALUES (" & CLng(Hands(i).g_HandID) & ",#" & Hands(i).g_HandDate & "#," & Hands(i).g_WasSeated & ")"

(also tried using CDate() and removing the # signs but to no avail)

also when i look at my acess DB, depending on the value of the day, the Database is half and half mm/dd/yyyy and dd/mm/yyyy

for example

30/08/2005 23:42:35
09/01/2005 18:32:13

eventualy this leads to erattic results when trying to retrieve rows on certain days.
(ie is 01/10/2005 or 10/01/2005 the value i want)

so i guess the question is, what is the best way of working with dates when writing to an access DB?

any input apreciated, this is driving me crazy

If somethings hard to do, its not worth doing - Homer Simpson

 

[RoyVidar]

Erm - I think your format makes it english (and if not, then you're doing some implicit casting, which I think might create what youre experiencing), you might want to change the datatype of g_HandDate to string, then the assigning to

[tt]... Mid(rs.ColumnValue(1), 5, 2) & "/" & Mid(rs.ColumnValue(1), 7, 2) & _
"/" & Mid(rs.ColumnValue(1), 1, 4) ...[/tt]

or use

[tt]... Mid(rs.ColumnValue(1), 1, 4) & "-" & Mid(rs.ColumnValue(1), 5, 2) & "-" & Mid(rs.ColumnValue(1), 7, 2) ...[/tt]

Roy-Vidar

 

[chiph]

If you're writing code to do this, make sure it stays as a Date variable. If you (at any point!) go through a string variable, things get ummm, interesting, as the conversion is likely to format it into whatever the control panel preferences are set to.

Chip H.


____________________________________________________________________
Donate to Katrina relief:

http://s1.amazon.com/paypage/PELYGQVJ8Q7IB/103-6821258-5919825

If you want to get the best response to a question, please read FAQ222-2244 first

 

[RoyVidar]

The bad casting here, is peformed when taking a string date, picking the elements of it, concatenate a new string and assign this concatenated string to a date variable. That's the implicit cast, which creates the "half and half", because the string is "UK".

To keep the g_HandDate datatype Date, I'd probably use DateSerial to add the date, then add TimeSerial for the time part, and then, one would need an explicit formatting when entering the dynamic SQL (i e format(g_HandDate, "yyyy-mm-dd hh:nn:ss"))

But then, in stead of the dynamic SQL faq709-1526 ;-)

Roy-Vidar

 

[vbSun]

VB uses two different date locales -
the system locale - Decides the output format of the date
the code locale - ALWAYS U.S. English.

MSDN recommends using the # delimiter while using dates in SQL, and it forces the date to be in the code locale - U.S. English and hence mm/dd/yyyy.

The confusion is Compounded with the fact that VB tries to be helpful and intelligent while interpreting a date. If you look at some examples,

#14/03/2005# -> 14th of March 2005
#03/14/2005# -> 14th of March 2005
#04/03/2005# -> 3rd of April 2005 :-0

So, it is recommended to ignore MSDN advice this time and pass the dates as preformatted string, since it will reduce one confusion (VB trying to be intelligent part).

Try these format strings.

[blue]
Global Const DateTimeFormat = "\#mm\/dd\/yyyy hh\:nn\:ss\#"
[/blue]

and then

[blue]
strSQL = strSQL & Format$(Now, DateTimeFormat)
[/blue]

------------------------------------------
The faulty interface lies between the chair and the keyboard.

 

[ADoozer]

thanks all.

i used RoyVidar's second solution (using dateserial and timeserial) then formating into the DB.

If somethings hard to do, its not worth doing - Homer Simpson