ASP - MySQL - Date conversion - NOOB

ragingbull24 · #1 (**permalink**) 02-16-05, 10:52

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

guelphdad · #2 (**permalink**) 02-16-05, 21:43

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.

r937 · #3 (**permalink**) 02-17-05, 00:47

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

ragingbull24 · #4 (**permalink**) 02-18-05, 04:11

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?

r937 · #5 (**permalink**) 02-18-05, 06:56

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?

healdem · #6 (**permalink**) 02-18-05, 08:38

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

ragingbull24 · #7 (**permalink**) 02-18-05, 09:04

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 ) 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>

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

</body>
</html>

guelphdad · #8 (**permalink**) 02-18-05, 09:50

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech