ScoobyNet.com - Subaru Enthusiast Forum

ScoobyNet.com - Subaru Enthusiast Forum (https://www.scoobynet.com/)

- Computer & Technology Related (https://www.scoobynet.com/computer-and-technology-related-34/)

- - MySQL date format (https://www.scoobynet.com/computer-and-technology-related-34/221647-mysql-date-format.html)

Dream Weaver

19 June 2003 06:38 PM

Anyone know if you can store the date in MySQL in the "normal" format?

Currently it stores dates as YYYY-MM-DD, which is odd, and a particular pain when using the ASP date function.

Anyone any ideas, other than creating a function to read or store the standard format?

Andrewza

19 June 2003 08:09 PM

Nope, I use MySQL with PHP and have functions to go from mysql date to unixtime and back. IIRC doesn't ASP use an even more annoying US format anyhow? (Hence various date bugs on this BBS ;) )

Dream Weaver

19 June 2003 11:07 PM

Never noticed any probs before Andy, just use date, or day(month), or now and it gives you the whole shebang :D:D

Looks like I need to write a function for it then.

AdrianFRST

20 June 2003 08:31 AM

function getdate(epoch)
'converts unix epoch date to UK date / time format...
getdate = dateadd("s", epoch, #1970-01-01#)
end function

:D

Dates in ASP depend on server locale. I've not had any problems with them, but then again I use either my own servers or Titan's, which are correctly set up for the UK.

Dream Weaver

20 June 2003 09:02 AM

Thats why I have also never had any probs then, all hosted with Titan :D:D

Cheers Ady, where to use it then? Presume that will read the date format from the db, then change it for displaying. I will also need an input function for adding the date as well.

Andrewza

20 June 2003 10:21 AM

if you're just inserting things as created into mysql you just do

insert into table (date) values(NOW()), which will give you now, however you've almost definitely got to edit the dates I suspect which means creating a function to do it unfortunately.

If you were using PHP:

Code:

/*

        Calculate unixtime from a MySQL DATETIME

*/



function unixtime_from_mysql($mysql_date) {



        if($mysql_date) {

                list( $date, $time ) = split( " " , $mysql_date );

                list( $year, $month, $day ) = split( "-" , $date );

                list( $hour, $minute, $second ) = split( ":" , $time);

                $unixtime = mktime( $hour , $minute , $second , $month , $day , $year );

                return $unixtime;

        } else {

                return 0;

        }



}



/*

        Calculate a MySQL format date from unixtime

*/



function mysql_from_unixtime($unixtime) {

        if($unixtime) {

                return date("Y-m-d H:i:s",$unixtime);

        } else {

                return "0000-00-00 00:00:00";

        } 



}

David_Wallis

20 June 2003 07:20 PM

how about simply...

temp = "2003-06-20"

mysqldate = Right(Date,4) & "-" & Mid(Date, 4,2) & "-" & Mid(Date, 7,2)
Wscript.echo mysqldate

AspDateFromDb = Right(temp,2) & "/" & Mid(Temp, 6,2) & "/" & Left(Temp ,4)
wscript.echo AspDateFromDb

legacyPete

21 June 2003 04:27 PM

Why not format it using MySQL?

select DATE_FORMAT(field, '%d/%m/%Y') AS myDate from table

Dream Weaver

26 June 2003 02:37 PM

strDate = year(now) & "/" & month(now) & "/" & day(now)

Seems to do the trick. :)

David_Wallis

26 June 2003 02:43 PM

cool.. I was the closest then :D :rolleyes:

All times are GMT +1. The time now is 12:42 AM.