/    Sign up×
Community /Pin to ProfileBookmark

mysql date_format problem

Copy linkTweet thisAlerts:
Mar 11.2008

HI

I have then below that is supposed to return a reformated date from a field in a mysql date field in my table.

users.spousedob = 1978-03-11

DATE_FORMAT(users.spousedob, ‘%a %b %e’)

The is returning Saturday, March 11 2008 but should be returning Tuesday, March 11 2008

Any ideas how to fix this

Thank you

to post a comment
PHP

6 Comments(s) ↴

Copy linkTweet thisAlerts:
@felgallMar 11.2008 — The is returning Saturday, March 11 2008 but should be returning Tuesday, March 11 2008[/QUOTE]

No it isn't. You are not displaying the year at all and the date you are retrieving is from 1978 not 2008 and so the correct day of the week for that date in 1978 is Saturday and not Tuesday.
Copy linkTweet thisAlerts:
@kprocauthorMar 11.2008 — how would i change it so that it uses the current year in my function
Copy linkTweet thisAlerts:
@TheRaveMar 11.2008 — This line might give you a clue:

users.spousedob = 1978-03-11
Copy linkTweet thisAlerts:
@kprocauthorMar 11.2008 — I understand why its happening, I'm not shure how to use mysql to change the value from 1978-03-11 to 2008-03-11

Any ideas how to do this
Copy linkTweet thisAlerts:
@felgallMar 11.2008 — Use an update call on the database that retrieves the date value and substitutes the current year saving it again. Unless the date in the database contains the right year you can't extract the correct day of the week from it.

If you don't want to actually change the date in the database then retrieving the date and then updating the year on the retrieved date before extracting the day of the week would be the easiest way.
Copy linkTweet thisAlerts:
@kprocauthorMar 12.2008 — here is the solution to my question


DATE_FORMAT( CONCAT( '2008-', MONTH(users.spousedob), '-', DAY(users.spousedob) ), '%a %b %e' )
×

Success!

Help @kproc spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 5.25,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @AriseFacilitySolutions09,
tipped: article
amount: 1000 SATS,

tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,
)...