insert date question

[angst]

ok,
I've got a mysql data base, where most of the date fields are on varchar(25),
i've made a new field that is a datetime field. what i've done is made a script to loop through the table and update the new field with the old dates, but in the right format. but the new field comes up black when i view the db.

currently varchar format: 11/21/03 9:31:51 AM

was wondering if someone could tell me how to format the existing date correctly to update the new datetime field?

thanks in advance for your time!
-Ken

[Brandoe85]

You should be able to run the current dates through date_format when you select them, and then insert. Although, does the format matter that much, when you can format it any way you'd like when you selelct the records.

Good luck

[angst]

ok,
this is what i've done so far:


Function FormatDateMySql(DateTemp)
StrDateTime = Split(DateTemp," ")
StrDate = StrDateTime(0)
StrTime = StrDateTime(1)
StrM = StrDateTime(2)

StrDatetmp = Split(StrDate,"/")
StrDay = StrDatetmp(0)
StrMonth = StrDatetmp(1)
StrYear = StrDatetmp(2)

FormatDateMySql = StrYear&"-"&StrMonth&"-"&StrDay&" "&StrTime&" "&StrM
End Function



output:
2003-08-13 7:55:17 PM
which is better, but the time is still not right.
should be:
2003-08-13 19:55:17

not sure how to change the time to 24 hour format.also remember that the input is in varchar format in the database, so i can't use any date time formatting on it.

thanks again,
-Ken

[Brandoe85]

What happends when you insert the varchar value directly into the datetime field? I would have thought you could have inserted that string into your new field, and then when you select you're records from that date field you can use date_format.

Good luck

[angst]

no, it just inserts a blank field.

[angst]

ok, got it.

this works:

Function FormatDateMySql(DateTemp)

StrDateTime = Split(DateTemp," ")
StrDate = StrDateTime(0)
StrTime = StrDateTime(1)
StrM = StrDateTime(2)

StrDatetmp = Split(StrDate,"/")
StrDay = StrDatetmp(0)
StrMonth = StrDatetmp(1)
StrYear = StrDatetmp(2)

StrTimeTmp = Split(StrTime,":")
StrHour = StrTimeTmp(0)
StrMin = StrTimeTmp(1)
StrSec = StrTimeTmp(2)

If StrM="PM" Then
StrHour = StrDatetmp(0) + 12
End if

FormatDateMySql = StrYear&"-"&StrMonth&"-"&StrDay&" "&StrHour&":"&StrMin&":"&StrSec
End Function

[angst]

ok, maybe this isn't working


Function FormatDateMySql(DateTemp)

StrDateTime = Split(DateTemp," ")
StrDate = StrDateTime(0)
StrTime = StrDateTime(1)
StrM = StrDateTime(2)

StrDatetmp = Split(StrDate,"/")
StrDay = StrDatetmp(0)
StrMonth = StrDatetmp(1)
StrYear = StrDatetmp(2)


StrTimeTmp = Split(StrTime,":")
StrHour = StrTimeTmp(0)
StrMin = StrTimeTmp(1)
StrSec = StrTimeTmp(2)

If NOT left(StrHour,1) = "0" Then
If StrM="PM" Then
StrHour = StrDatetmp(0) + 12
End if
End If

FormatDateMySql = StrYear&"-"&StrMonth&"-"&StrDay&" "&StrHour&":"&StrMin&":"&StrSec
End Function


it converts most varchar date/times to mysql format ok, but many of them come up 0000-00-00 00:00:00 even though the orginal dates are set the same as the rest.
an example of one that didn't convert: 26/06/2003 12:15:02 PM
and one that did: 26/06/2003 11:48:42 AM

i don't seen any difference..

-Ken

[glenngv]

Try this:
Function FormatDateMySql(DateTemp)
Dim yyyy, mm, dd, hh, nn, ss
If IsDate(DateTemp) Then
yyyy = Year(DateTemp)
mm = Month(DateTemp)
dd = Day(DateTemp)
hh = Hour(DateTemp)
nn = Minute(DateTemp)
ss = Second(DateTemp)
FormatDateMySql = yyyy & "-" & PadZero(mm) & "-" & PadZero(dd) & " " & PadZero(hh) & ":" & PadZero(nn) & ":" & PadZero(ss)
End If
End Function

Function PadZero(Num)
If Num < 10 Then
PadZero = "0" & Num
Else
PadZero = Num
End If
End Function