How to convert date to a format mm dd yyyy

0 votes

I have a SQL table with a date field entitled CREATED TS that contains dates in various formats, such as. as depicted below:

Feb 20 2012 12:00AM
11/29/12  8:20:53 PM          
Feb 20 2012 12:00AM
11/29/12  8:20:53 PM          
Feb 20 2012 12:00AM
11/29/12  8:20:53 PM          
Nov 16 2011 12:00AM
Feb 20 2012 12:00AM
11/29/12  8:20:52 PM

Now that I'm comparing the dates in the WHERE clause of my SELECT query, I want to change these to the format mmddyyyy before.

I tried using:

CONVERT(VARCHAR(10),CREATED_TS,101)

But got the result as,

Feb 20 201
11/29/12  
Feb 20 201
11/29/12  
Feb 20 201
11/29/12  
Nov 16 201
Feb 20 201
11/29/12  

I need the result eg. 02/20/2012 in order to compare. 

Sep 8, 2022 in Database by Kithuzzz
• 38,020 points
4,181 views

1 answer to this question.

0 votes

Your data is already in varchar, thus you must first convert it to date:

select convert(varchar(10), cast(ts as date), 101) from <your table>

I hope this helps you.

answered Sep 10, 2022 by narikkadan
• 63,680 points

Related Questions In Database

+1 vote
1 answer

how to change format of date from mm/dd/yyyy to dd-mmm-yyyy in MS Excel

First, pick the cells that contain dates, ...READ MORE

answered Apr 11, 2022 in Database by gaurav
• 23,260 points
158,657 views
0 votes
0 answers
0 votes
0 answers

Convert date from dd-mm-yyyy to yyyy-mm-dd in SQL Server

Given date: DECLARE @Date1 VARCHAR(50) = '30-01-2015' I want ...READ MORE

Aug 18, 2022 in Database by Kithuzzz
• 38,020 points
6,123 views
0 votes
1 answer

Convert Date format into DD/MMM/YYYY format in SQL Server

Using convert() and style 106, you can ...READ MORE

answered Feb 23, 2022 in Database by Vaani
• 7,070 points
13,993 views
0 votes
0 answers

How to update Identity Column in SQL Server?

With 200 records currently, I want to ...READ MORE

Aug 9, 2022 in Database by Kithuzzz
• 38,020 points
1,326 views
0 votes
0 answers

Simple DateTime sql query

How do I query the DateTime database ...READ MORE

Aug 12, 2022 in Database by Kithuzzz
• 38,020 points
595 views
0 votes
0 answers

How can I select the first day of a month in SQL?

I only need to choose the given ...READ MORE

Aug 14, 2022 in Database by Kithuzzz
• 38,020 points
595 views
0 votes
0 answers

CROSS JOIN vs INNER JOIN in SQL

What is the difference between CROSS JOIN and INNER JOIN? CROSS ...READ MORE

Aug 18, 2022 in Database by Kithuzzz
• 38,020 points
479 views
0 votes
1 answer

How to calculate percentage with a SQL statement

The following has passed my tests, and ...READ MORE

answered Sep 12, 2022 in Database by narikkadan
• 63,680 points
5,764 views
0 votes
1 answer

How to select from multiple tables without a JOIN ?

Try this: SELECT ... FROM ( SELECT ...READ MORE

answered Sep 12, 2022 in Database by narikkadan
• 63,680 points
6,244 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP