Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2005
    Posts
    4

    Question Unanswered: ASP - MySQL - Date conversion - NOOB

    Hi,
    Need enter some dates into a MySQl database from an ASP page but they keep being entered in the wrong format as the date structure is different in the two technologies using "date" fields ( 0000-00-00 v 00-00-0000 ). Whats the best way of doing it as this is becoming a bit of a ball swelling task?

    examples and dummy tutorials welocome

  2. #2
    Join Date
    Mar 2004
    Posts
    480
    yyyy-mm-dd is the only way to insert dates in mysql. look up date_format in the manual. you can display any number of options.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    actually, there are several acceptable formats --

    - a string 'YYYY-MM-DD'
    - a string 'YY-MM-DD'
    - a string 'YYYYMMDD'
    - a string 'YYMMDD'
    - a number YYYYMMDD
    - a number YYMMDD

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2005
    Posts
    4

    Please Assist

    Quote Originally Posted by guelphdad
    yyyy-mm-dd is the only way to insert dates in mysql. look up date_format in the manual. you can display any number of options.
    Only being able to input yyyy-mm-dd to Mysql was why I posted the thread. I need to convert the ASP date which is dd-mm-yyyy so that Mysql will accept it.

    Any ideas anyone please?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you can rearrange the pieces of the date using either sql or asp

    my suggestion is to do it with asp, it is a scripting language

    is that what you were asking?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    assuming that ASP using vbscript has the same or similar functions to VB/VBA then have a look at the date maniputlation fucntions such as year, month & day

  7. #7
    Join Date
    Feb 2005
    Posts
    4

    Googled

    After Googleing. I came across a related page which gave me the following sollution. Sadly I couldnt get it to work it just gives me a blank 00-00-00 and not the converted date. While the the structure looks acceptable, not sure whats happening to the actual date.

    Is there anyone out there who has used ASP with Mysql dates?

    <%
    '====================
    function mysqlDate( d, dir )
    '====================
    'if not isDate( d ) then call errorMessage( d & " is not a date " )
    'if not isDate( d ) then exit function
    if not isDate( d ) then d = Date()

    dim strNewDate
    select case dir

    case 1 '=== store in db
    strNewDate = year( d ) & "-" & month( d ) & "-" & day( d )

    case 2 '=== use with asp
    strNewDate = month( d )& "/" & day( d ) & "/" & year( d )
    end select

    strNewDate = cDate( strNewDate )
    mysqlDate = strNewDate
    end function


    '====================
    function mysqlTime( t, dir )
    '====================

    dim strSuffix, arTime, i, x

    t = trim( Lcase( t ) )
    if inStr( t, "pm" ) > 0 OR inStr( t, "am" ) > 0 then
    strSuffix = right( t, 2 )
    t = left( t, inStr( t, strSuffix ) -2 )
    t = trim( t )
    end if

    for i = 1 to len( t )
    x = mid( t, i, 1 )
    if not isReallyNumeric( x ) and x <> ":" then t = replace( t, x, "" )
    next

    arTime = split( t, ":" )
    t = ""
    for i = 0 to 2
    if uBound( arTime ) < i then redim preserve arTime( i )

    if i = 0 then
    if dir = 1 then
    if strSuffix = "pm" and cInt( arTime( i ) ) < 12 then
    arTime( i ) = cInt( arTime( i ) ) + 12
    end if
    else
    if cInt( arTime( i ) ) > 12 then
    arTime( i ) = cInt( arTime( i ) ) - 12
    strSuffix = "PM"
    else
    strSuffix = "AM"
    end if
    end if
    end if

    do until len( arTime( i ) ) = 2
    arTime( i ) = "0" & arTime( i )
    loop

    t = t & arTime( i )
    if i < 2 then t = t & ":"
    next
    arTime = null
    if dir = 2 then t = t & " " & strSuffix
    'debug( t )
    mysqlTime = t
    end function

    %>

    <html>
    <head>
    <title>Date Conversion Test</title>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
    </head>

    <body>

    <P>Normal Date: <%response.write(date())%></P>
    <P>MySQL Date: <% Response.Write(mysqlDate(1, Date)) %></P>


    </body>
    </html>

  8. #8
    Join Date
    Mar 2004
    Posts
    480
    You're right Rudy, I need better sleep patterns. I should have specified that it was year-month-day and then yes specified the patterns as you outlined above.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •