Re: select date_format('2004-10-03 15:06:14','%m/%d/%y %T');

[Daniel Kasak]

Scott Hamm wrote:
How do I create a table using:
date_format('2004-10-03 15:06:14','%m/%d/%y %T');
That way I can import Access Data?
 

Can't do that.
You can import Access data via ODBC - that way the driver figures out 
how to send the data to MySQL.
I assume you are trying to export to a text file and then import from that?

The easiest way of doing this is to export your data in the correct format:
select date_format(MyDateField, 'yyyy-mm-dd') as FormattedDateField
I have attached a VB function that does some fancy reformatting of 
DateTime fields. You shouldn't need it in this case ( see above ), but 
it could be handy for later. I use it this way:

- Alter the table and change your date or datetime field to a 
varchar(50) field.
- Import into the table
- run the attached reformat_dates() function on the table / field, which 
will format the data the correct way, and change it back to a date or 
datetime field

Hope it helps :)
--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au
Function ReformatDates(MyTable, MyField, _
 Optional YearDigits As Integer, Optional IncludesTime As Boolean, _
 Optional MonthsInText As Boolean) As Integer
If YearDigits = 0 Then YearDigits = 2

Dim myconn As ADODB.connection, mycommand As ADODB.Command, _
 sqlstr As String

Set myconn = New ADODB.connection
With myconn
 .ConnectionString = xxxxxxxxxxx ' insert your connection string here ...
 .Open
End With

Set mycommand = New ADODB.Command
With mycommand
 .ActiveConnection = myconn
 .CommandType = adCmdText
End With

' Add tmp field
sqlstr = "alter table " & MyTable _
 & " add column " & MyField & "_tmp " _
 & IIf(IncludesTime, "datetime", "date") & " NULL after " & MyField

mycommand.CommandText = sqlstr
mycommand.Execute

' Populate tmp field
' NOTE: Supported date formats:
    ' d/mm/yy
    ' dd/mm/yy
    ' d/mm/yyyy
    ' dd/mm/yyyy
    ' dd MMM yy
    ' dd MMM yyyy

If MonthsInText Then
    
    sqlstr = "update " & MyTable & " set" & vbNewLine _
     & " " & MyField & "_tmp=" & vbNewLine _
     & " concat(right(" & MyField & "," & YearDigits & "),'-'," & vbNewLine _
     & "  case substring(" & MyField & ",length(" & MyField & ")-" _
     & IIf(YearDigits = 4, 7, 5) & ",3)" _
     & "   when 'Jan' then 01" & vbNewLine _
     & "   when 'Feb' then 02" & vbNewLine _
     & "   when 'Mar' then 03" & vbNewLine _
     & "   when 'Apr' then 04" & vbNewLine _
     & "   when 'May' then 05" & vbNewLine _
     & "   when 'Jun' then 06" & vbNewLine _
     & "   when 'Jul' then 07" & vbNewLine _
     & "   when 'Aug' then 08" & vbNewLine _
     & "   when 'Sep' then 09" & vbNewLine _
     & "   when 'Oct' then 10" & vbNewLine _
     & "   when 'Nov' then 11" & vbNewLine _
     & "   when 'Dec' then 12" & vbNewLine _
     & "  end,'-',"
     
    sqlstr = sqlstr & " left(" & MyField & ", 2))"
    
Else
    
    sqlstr = "update " & MyTable & " set" _
     & " " & MyField & "_tmp=" _
     & "  case when substring(" & MyField & ",2,1)='/'" _
     & " then concat(" _
     & " substring(" & MyField & ",6," & YearDigits & "),'-'," _
     & " substring(" & MyField & ",3,2),'-'," _
     & "'0', substring(" & MyField & ",1,1)"
    
    If IncludesTime Then
        sqlstr = sqlstr & ", ' '," _
         & " case when right(" & MyField & ", 2)='PM'" & vbNewLine _
         & "  then cast(substring(" & MyField & ",12,2) as unsigned)+12" _
         & "  else substring(" & MyField & ",12,2) end, ':'," _
         & " substring(" & MyField & ",15,2), ':'," _
         & " substring(" & MyField & ",18,2)"
    End If
    
    sqlstr = sqlstr & ") else concat(" _
     & " substring(" & MyField & ",7," & YearDigits & "),'-'," _
     & " substring(" & MyField & ",4,2),'-'," _
     & " substring(" & MyField & ",1,2)"
    
    If IncludesTime Then
        sqlstr = sqlstr & ", ' '," _
         & " case when right(" & MyField & ", 2)='PM'" & vbNewLine _
         & "  then cast(substring(" & MyField & ",12,2) as unsigned)+12" _
         & "  else substring(" & MyField & ",12,2) end, ':'," _
         & " substring(" & MyField & ",15,2), ':'," _
         & " substring(" & MyField & ",18,2)"
    End If
    
    sqlstr = sqlstr & ") end"
    
End If

mycommand.CommandText = sqlstr
mycommand.Execute

' Drop old field
sqlstr = "alter table " & MyTable _
 & " drop column " & MyField
mycommand.CommandText = sqlstr
mycommand.Execute

' Rename tmp field
sqlstr = "alter table " & MyTable _
 & " change " & MyField & "_tmp " & MyField & "" _
 & IIf(IncludesTime, " datetime", " date") & " NULL"
mycommand.CommandText = sqlstr
mycommand.Execute

myconn.Close
Set mycommand = Nothing
Set myconn = Nothing

End Function

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]