SEO

#1 (**permalink**) 02-28-2008, 08:13 AM

If the value stored in the adate column is 2006-09-24, how can the
following statement be modified to return
20060924 vs. 2006924?

SELECT concat(year(adate),month(adate),day(adate)) from demotable

or is there a better way to retrieve a date value in the format
YYYYMMDD?

thanks
Lee

#2 (**permalink**) 02-28-2008, 08:13 AM

>If the value stored in the adate column is 2006-09-24, how can the
>following statement be modified to return
>20060924 vs. 2006924?
>
>SELECT concat(year(adate),month(adate),day(adate)) from demotable
>
>or is there a better way to retrieve a date value in the format
>YYYYMMDD?

I suggest:
SELECT date_format(adate, '%Y%m%d') from demotable;

which allows a lot more flexibility with formats.

Gordon L. Burditt

#3 (**permalink**) 02-28-2008, 08:13 AM

On Thu, 21 Sep 2006 02:19:53 GMT, Lee Peedin wrote:
> If the value stored in the adate column is 2006-09-24, how can the
> following statement be modified to return
> 20060924 vs. 2006924?
>
> SELECT concat(year(adate),month(adate),day(adate)) from demotable
>
> or is there a better way to retrieve a date value in the format
> YYYYMMDD?

mysql> select date_format('2006-09-26', '%Y%m%d');
+-------------------------------------+
| date_format('2006-09-26', '%Y%m%d') |
+-------------------------------------+
| 20060926 |
+-------------------------------------+
1 row in set (0.04 sec)

mysql> select date_format('2006-09-03', '%Y%c%d');
+-------------------------------------+
| date_format('2006-09-03', '%Y%c%d') |
+-------------------------------------+
| 2006903 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> select date_format('2006-09-03', '%Y%c%e');
+-------------------------------------+
| date_format('2006-09-03', '%Y%c%e') |
+-------------------------------------+
| 200693 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql>

See http://dev.mysql.com/doc/refman/5.0/...functions.html
for even more fun and games.

--
Every normal man must be tempted at times to spit upon his hands, hoist
the black flag, and begin slitting throats.
-- HL Mencken

#4 (**permalink**) 02-28-2008, 08:13 AM

Peter & Gordon :-)
Thanks to both of you - as you can probably tell, I'm a newbie. Your
responses were exactly what I needed.

Lee

BTW: Unless you're as old as I am, you probably won't understand the
smiley following "Peter & Gordon".
<http://www.classicbands.com/pandg.html>

On Thu, 21 Sep 2006 02:38:56 -0000, gordonb.g22xl@burditt.org (Gordon
Burditt) wrote:

>>If the value stored in the adate column is 2006-09-24, how can the
>>following statement be modified to return
>>20060924 vs. 2006924?
>>
>>SELECT concat(year(adate),month(adate),day(adate)) from demotable
>>
>>or is there a better way to retrieve a date value in the format
>>YYYYMMDD?
>
>I suggest:
>SELECT date_format(adate, '%Y%m%d') from demotable;
>
>which allows a lot more flexibility with formats.
>
> Gordon L. Burditt

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Retrieve month portion of date column as 2 digits