/    Sign up×
Community /Pin to ProfileBookmark

Mysql Date field store in text formatted MMDDYY (01012007)

Copy linkTweet thisAlerts:
Jan 02.2007

Hi,

I have saved some date fields in the above format, and it was working okay until the change of the year. Now, when I try to do a search of dates for
SELECT *WHERE ord.MMDDYYYY Between ‘01011999’ And ‘01012007’ order by ord.ID I have no records returned.

I have tried casting the record values to a date
—- select * where cast(ord.MMDDYY as DATE) between cast(‘01011999 as Date) and cast(‘01012007’ as DATE) —- But that doesnt work

I really kind of need to keep the database value as a text value if possible.

Any suggestions?

to post a comment
PHP

2 Comments(s) ↴

Copy linkTweet thisAlerts:
@NightShift58Jan 02.2007 — I'm not sure I understand how you stored the data. You mention MMDDYYYY.

How and where did you specify this format?

If that's really the format used, doing [B]cast('01011999 as Date)[/B] won't help you because mysql's [B]date[/B] format is YYYY-MM-DD, which will then translate the cast value to "0000-00-00" - and that's not going to get you closer to your goal.

You should consider using mysql date's format and type, as there are few penalties for doing so. When you retrieve the date information, it will be delivered as a string and when searching, you can apply string values against the content of the date fields.
Copy linkTweet thisAlerts:
@NogDogJan 02.2007 — I agree 100% with NightShift that you should store dates in the DB as date or datetime fields, not as varchar (and you can use the date_format() MySQL function when querying the field to output it in the desired format). In the meantime, if it's not possible to change that column at this time, you could do the following:
<i>
</i>SELECT * WHERE STR_TO_DATE(ord.MMDDYYYY, '%m%d%Y')
BETWEEN '1999-01-01' AND '2007-01-01' ORDER BY ord.ID
×

Success!

Help @mparker1113 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.18,
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,
)...