I have tried the column as a varchar and timestamp but it does not work. with the Timestamp setup and DATE_FORMAT() I get 00-00-0000 at the moment and I know that there are dates in there. The reason for this is the import changes all the field to 00000000000000 which is the TimeStamp default.
I have also renamed the column to ReleaseDate in case there was a problem using a reserved word and there was still no fix. ----- Original Message ----- From: "CPT John W. Holmes" <[EMAIL PROTECTED]> To: "Morgan Bardon" <[EMAIL PROTECTED]>; "Moreno Riccardi" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, July 25, 2003 4:44 PM Subject: Re: [PHP-DB] MYSQL and PHP cast() function... > > Thanks for that, but I get a NULL entry in the field then for all the > dates. > > What column type is the DATE column? Also, since DATE is a reserved word in > MySQL, naming a column DATE probably wasn't a good idea. If you know you > always want the first 11 characters, you can always do LEFT(Date,11) to > retrieve them. DATE_FORMAT() will only work if your column is a date or time > column, i.e. DATE, DATETIME, or TIMESTAMP. > > ---John Holmes... > > > ----- Original Message ----- > > From: "Moreno Riccardi" <[EMAIL PROTECTED]> > > To: "Morgan Bardon" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > > Sent: Friday, July 25, 2003 4:34 PM > > Subject: R: [PHP-DB] MYSQL and PHP cast() function... > > > > > > > Try > > > $query2 = "select Title, date_format(Date,'%d-%m-%Y) as Date, Artist, > > > Country, > > > RecordCompany, CatalogueNumber, Composer FROM recording where title > regexp > > > '$keyword'"; > > > > > > Hi, > > > Moreno > > > -----Messaggio originale----- > > > Da: Morgan Bardon [mailto:[EMAIL PROTECTED] > > > Inviato: venerd́ 25 luglio 2003 17.17 > > > A: [EMAIL PROTECTED] > > > Oggetto: [PHP-DB] MYSQL and PHP cast() function... > > > > > > > > > Hi there, > > > > > > I have been trying to get a solution to this select query from all over > > the > > > web and have found no answer so I was hoping someone here could help > out. > > > The date field of the database is imported from a text file with the > date > > > format 'dd-mm-yyyy hh:mm:ss' and I only want to display the first 11 > > > characters of the field. > > > > > > The select statment is am using is > > > $query2 = "select Title, cast(Date as varchar(11)) Date, Artist, > Country, > > > RecordCompany, CatalogueNumber, Composer FROM recording where title > regexp > > > '$keyword'"; > > > > > > However when I try to use this I get an error about the cast function as > > > follows: > > > > > > You have an error in your SQL syntax near '(Date as varchar(11)) Date, > > > Artist, Country, RecordCompany, CatalogueNumber, Com' at line 1 > > > > > > Can anyone help me with the cast() function. I have gotten this to work > on > > > an internal MSSQL server but not on the MySQL server. > > > > > > thanks > > > > > > Morgan Bardon > > > > > > > > > > > > > > > > > > -- > > > PHP Database Mailing List (http://www.php.net/) > > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > > > > > > > > > > > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
