SQL Select Query with single field as formatted and remaining fields as wildcard

I'm trying to fetch records from a table in MySQL 5.5 (Community Server) which has 22 fields in total, the first field is of type DATETIME, and I want that field formatted with DATE_FORMAT() method, while I want to fetch remaining fields as they are. So basically what I'm trying to do is something like this

SELECT DATE_FORMAT(fdate,'%r %d-%m-%Y'),* FROM userdetails;

While I know this is invalid syntax, and currently I'm accomplishing this by selecting all the remaining fields manually which obviously makes the query longer. So is there a more elegant way to do the same? I also thought to use two different queries where 1st will fetch the formatted date and second will be usual

开发者_如何学编程

SELECT * FROM userdetails;

But since I fetch records sorted by date field itself, I guess using two different queries may lead to conflicts on order of records returned.

---

You can use

SELECT  DATE_FORMAT(fdate,'%r %d-%m-%Y'), ud.*
FROM    userdetails ud

which will select all columns (including the original fdate) along with the formatted value.

There is no way to select "almost all columns" except listing them explicitly.

---

Why don't you do the date formatting in the application code? Let the database handle the data and let the application handle presentation.

---

In MySQL you can reverse it and put the * first and then columns you want to specify. You'll just get two fdate columns. If you're using hashes in perl or associative arrays in PHP, or the equivalent in other languages, to fetch the rows then you'd end up having that index overwritten.

Best way to deal with it is to name the column:

SELECT *, DATE_FORMAT(fdate,'%r %d-%m-%Y') AS formatted_date FROM userdetails;

Now you essentially have two date columns, you just used the named one when you want to use your date.

---

DATA_FORMAT(fdate,'%r %d-%m-%Y').....

wont work

DATE_FORMAT(fdate,'%r %d-%m-%Y')....

---

SELECT DATE_FORMAT(fdate,'%r %d-%m-%Y') AS `formattedDate` , * FROM userdetails;

That should do the trick. Can't se why your first effort fails, you should check the field names it returned - suspect you'll find one called DATE_FORMAT( ....