T-SQL Date Format

Question

How to convert 19 Dec 2012 to date format returned by getdate() 2012-12-22 . I need this for date comparison because if I compare 19 Dec 2012 with 19 Nov 2012 , 19 Nov 2012 will turn out to be greater date due to alphabetical comparison.

EDIT

I am not database designer for this table , was given table as it is to work on .Otherwise would not have used varchar to store date

I have a column with entries like 19 Dec 2012 type varchar().. — Mudassir Hasan, Dec 22 '12 at 08:05
[Bad habits to kick : choosing the wrong data type](https://sqlblog.org/2009/10/12/bad-habits-to-kick-choosing-the-wrong-data-type) - you should always use the most appropriate data type - that's what they're there for, after all! Dates should **always** be stored as `DATE` (or `DATETIME`). If you already have those in your table as `varchar` - convert *that* to a `DATE` or `DATETIME` for comparison - not the other way around! — marc_s, Dec 22 '12 at 08:06
SELECT convert(varchar, getdate(), 106) results in 19 Dec 2012 , isn't there a way to convert the opposite way .. — Mudassir Hasan, Dec 22 '12 at 08:07
all most the same question where i give my answer [Stack overflow Sql Query to Convert date format ][1] [1]: http://stackoverflow.com/questions/13965837/sql-query-to-convert-date-format-to-another/13965919#13965919 — A.Goutam, Dec 22 '12 at 17:02
@A.Goutam..Thanks a ton for the link of different date formats..really helpful I have downloaded that page for quick reference in future.. — Mudassir Hasan, Dec 22 '12 at 17:19

John Woo · Accepted Answer · 2012-12-22T08:08:46.657

6

Try, but this returns a string and not a date anymore

SELECT convert(varchar, getdate(), 106)

SQLFiddle Demo

you need to convert it to date first before comparing to GETDATE.

SELECT convert(date, '19 Dec 2012', 106)

SQLFiddle Demo

Are you the one that designed the database? If yes, the next time you design (or if you have time to alter) use the appropriate data type.

edited Dec 22 '12 at 08:08

answered Dec 22 '12 at 08:03

John Woo

258,903
69
498
492

`SELECT convert(date, '19 Dec 2012', 106)` – John Woo Dec 22 '12 at 08:06
@MahmoudGamal `120 or 120` has time on it. – John Woo Dec 22 '12 at 08:07

score 6 · Answer 2 · answered Dec 22 '12 at 08:11

Convert both to dates. Don't use string to store date values! Even better, change your database schema and the string mismatch problems go away completely.

Also, please note that Getdate() does not return a string. It returns a date in the datetime data type. The client converts that to a string representation.

For more information, go read on "cast and convert" in SQL Server Books Online.

Convert (datetime, stringdate1)

To fix your broken database column do this:

ALTER TABLE YourTable ALTER COLUMN BadStringDate datetime;

T-SQL Date Format

2 Answers2