Need to convert varchar type having date in formate '03/28/1990' to convert it in to datetime

Question

1.00/5 (1 vote)

See more:

I have try this

SELECT DATE_FORMAT(dob,'%d%m%Y'),dob FROM abc;

not working.

What I have tried:

I have try this

SELECT DATE_FORMAT(dob,'%d%m%Y'),dob FROM abc

Posted 27-Apr-20 6:03am

Member 11776570

Updated 27-Apr-20 6:22am

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2020-04-27T06:10:00

Basically, don't.

Never store dates in strings - use DATETIME, DATE, or TIMESTAMP columns instead, and convert the dates to them in your presentation language (where you can cope with different user input formats).

Using strings is "easy", but it leaves you wide open for major problems later on - like date in ISO format: 2020-04-27, or US format: 04-27-2020, or just "hello" if you haven;t done any checking on your user inputs.

Change your db and store data in appropriate data types - it may seem more cumbersome, but it saves a lot of grief later on.

Wendelius · Accepted Answer · 2020-04-27T06:22:00

Solution 2

I fully agree with OriginalGriff, if possible do store the dates using appropriate data type. Not only the storing is more easy but also calculating, formatting etc is easy to do when the base type is date.

Having that said, in case you are absolutely not able to change the type you should be able to use

SQL

str_to_date(dob, '%m/%d/%Y')

For more information, see
MySQL :: MySQL 8.0 Reference Manual :: 12.6 Date and Time Functions[^]

Posted 27-Apr-20 6:22am

Wendelius

Comments

Member 11776570 27-Apr-20 12:32pm

Thanks that really works. Thanks a lot. I was just stuck into it. Already working from too many hours. Thanks buddy.

Member 11776570 28-Apr-20 8:16am

This error is occur now

The given value of type String from the data source cannot be converted to type datetime of the specified target column.

when i use this

DATE_FORMAT(STR_TO_DATE(dob, '%m/%d/%Y'),'%Y%e%c')

to store it in datetime datatype column.
Any suggestion for it.

Wendelius 28-Apr-20 12:16pm

Why do you convert it to string? As far as I can see you don't need DATE_FORMAT. Just try something like

INSERT INTO MyTable (....)
SELECT STR_TO_DATE(dob, '%m/%d/%Y'),
...
FROM ...

phil.o · Accepted Answer · 2020-04-27T06:23:00

Please see solution 1 for how you should treat datetime values in the future.

Now, for your current issue, there is a mismatch between the input you specified ('03/28/1990', i.e. month/day/year) and the format string you are passing to the DATE_FORMAT function ('%d%m%Y', i.e. day/month/year).

Need to convert varchar type having date in formate '03/28/1990' to convert it in to datetime

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0