convert varchar datetime to datetime while selecting from sql

Question

I have one table with field name DateTime (varchar(30)) and I want to compare this field to the same date and time formate. Below is the ss what I am doing. I am getting some date NULL and date formate returning wrong I want M/D/Y H:i:s but it returns (11/09/19 12:32:40) to 2011-09-19 12:32:40

Can anyone please help me to get right date with DateTime in SQL select query. so, I can compare it.

What have you tried so far? Where are you stuck? Please share the table structure, sample input data, expected output, and your attempts — Nico Haase, Feb 06 '20 at 07:27
@NicoHaase I already put the type of the field and date formate and what result I want. There is no need to add whole database structure. — Shefali, Feb 06 '20 at 07:34
Does this answer your question? [how to convert a string to date in mysql?](https://stackoverflow.com/questions/5201383/how-to-convert-a-string-to-date-in-mysql) — Wilmerton, Feb 06 '20 at 09:44
@Shefali SELECT CONVERT(varchar, '10/28/19 05:14:10 PM', 22); — AlexM, Feb 06 '20 at 09:45

score 2 · Accepted Answer · answered Feb 06 '20 at 07:27

2

You need to use STR_TO_DATE, not DATE_FORMAT. For your dates, you need this format:

 %m/%d/%y %l:%i:%s %p

For example

SELECT STR_TO_DATE('10/28/18 05:14:10 PM', '%m/%d/%y %l:%i:%s %p')

Output:

2018-10-28 17:14:10

answered Feb 06 '20 at 07:27

Nick

138,499
22
57
95

Thanks, the right solution. I Forgot about this function. – Shefali Feb 06 '20 at 07:31
I accepted this answer. Welcome – Shefali Feb 06 '20 at 07:40

score 0 · Answer 2 · answered Feb 06 '20 at 07:30

0

Try this

CONVERT(datetime,getDate(),22);

answered Feb 06 '20 at 07:30

Stevan Lai

183
1
1
11

Or you can check the reference here https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/ – Stevan Lai Feb 06 '20 at 07:32

convert varchar datetime to datetime while selecting from sql

2 Answers2