By BaDu666 on

Hi,

This is my first post here.

Im developing a site using drupal (very good btw) but ive run into a snag when using db_query:
if i run a query like:
db_query('SELECT DATE_FORMAT('2006-01-01', '%d-%m-%Y');');
I expect to get "01-01-2006" instead of that i get "0-01-2006". (notice the missing '1' in de day)

Ive figured out that this was causes by the modifiers in de db_query function (DB_QUERY_REGEXP intervenes)
if i use
mysql_query('SELECT DATE_FORMAT('2006-01-01', '%d-%m-%Y');');
I get the correct result "01-01-2006".

My question is how can i use db_query and use de %d in query's? As it is normal in Holland to use two digit day's.

thnx
Bas

⋅ Categories: Drupal 4.7.x

Comments

ukdg_phil’s picture

ukdg_phil commented

Hi BaDu666,

Off the top of my head (untested - & so maybe wrong) I think you can use the backslash '\' character to force the '%d' to skip drupals processing.

E.g:

db_query('SELECT DATE_FORMAT('2006-01-01', '\%d-%m-%Y');');

Not at a pc that I can test this - so sorry if that's incorrect...

erdemkose’s picture

erdemkose commented

You must use double %.

db_query('SELECT DATE_FORMAT('2006-01-01', '%%d-%%m-%%Y')');

--------------------------------------------------------------
http://erdemkose.com/
Drupal Turkiye

heine’s picture

heine commented

Use %%d (see also http://api.drupal.org/api/4.7/function/db_query and http://api.drupal.org/api/4.7/function/_db_query_callback).

Also, pay attention to your quotes; you cannot use internal single quotes here, unless you escape or enclose the string in double qoutes "" .

--
The Manual | Troubleshooting FAQ | Tips for posting | How to report a security issue.

BaDu666’s picture

BaDu666 commented

Wow thats fast!!!!

Thnx everyone for their quick response.
@erdemkoze: Indeed i had to use double % and not single,
The first suggestion (by ukdg_phil) with \% did not work (i tested that before i started this thread) and to be fair he was also not very shure ;).

@Heine: I'm sorry, i made an error in quoting the snippet here, in my code i used the double quotes to enclose the string.

Again: wow! fast response! I hope i get a response at the same speed if i have more questions :D

ukdg_phil’s picture

ukdg_phil commented

Sorry, my bad - Thanks for correcting me guys!