Changing the format of a date field via a Select Statment?

[229257]

I am quite new to SQL this may be a simple problem.

I have a column with month and year values in it. all values are similar to 200207 and 200205 4 digit year and 2 digit month.

When i do a select statement at the moment the coloumns come as follows:

rec_id | month | e.g select * from orders
------ -------
1 200205
2 200207

I would like a select statement that changes the format of the way the date is displayed without changing the structure of the table. The field type for date is int.

ideally this is how i would like it to look like:

rec_id | month2 |
------ ----------
1 May 2002
2 July 2002

Any help would be much appreciated.

If really necessary I could change the structure!

Thanks

 

[KarveR]

mysql> select @mydate:='your_date_field',date_format((@mydate*100),"%Y %M"

***************************************
Party on, dudes!

 

[Coderifous]

KarveR:

What's the @ symbol do? What's the *100 part do?

I'm a perl hacker who uses MySQL, but am not adept at all things MySQL. Wouldn't mind the extra brain food.

Thanks.

--jim

 

[KarveR]

OK
select
-> @mydate:='your_date_field' // this bit sets the user variable mydate equal to the value of your_daye_field (month is it?)

-> date_format( // this is standard date formating which has sooo many was to format a date its amazing, read up at mysql.com

this bit says multiply our variable for this call by 100.(@mydate*100) // your dates dont show any day, and we need the extra digits so that date format works correctly.
therefore 200205 (May 2002) x 100 = 20020500 or in date format land its the 00th of May 2002, but we never ask for a day so it never fails with a null value.

this bit selects year and month .,"%Y %M"
***************************************
Party on, dudes!

 

[229257]

Thanks to the above for pointing me in the write direction!!!

JIM

Not at all sure what the @ part does, i couldn't get it working using this method or any of theses methods. But *100 was a god send.

I did understand the concept of the * 100 which allowed me do what i wanted.

the * 100 takes a date value say 200208 this isn't reconized by Mysql as valid date format so when you try to use the DATE_FORMAT function to rearrange the date into a format you would like say 'August 2002' it give the wrong result.

when you multiply the date by 100 you get 20020800 this does work with the DATE_FORMAT and will give the write result e.g.

select month, date_format( month * 100, '%M %Y') as anchor from orders group by month order by month asc;

 

[Coderifous]

Thanks much fellas. It occurred to me that you were mulitplying there, just didn't understand why. Now I do.

As far as the @myvariable:='field_in_table' I'm guessing that the variable created here is only available throughout the scope of that SQL statement. Is this correct? I mean, you can't use it anywhere else (like in a subsequent select statement?).

Muchos Gracias.

--jim

 

[KarveR]

It should remain set throughout your session.
I primarily used it because it works quickly if you want to update all the fields with a new date format.
Guess it was down to version of mysql if its not working but you gotta watch for the : and ensure that all parenthesis are present. ***************************************
Party on, dudes!