CODEWITHSUNDEEP
sqlsql-servert-sql
Mudassir Hasan
Mudassir Hasan

Reputation: 28741

T-SQL Date Format

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

Upvotes: 1

Views: 2556

Answers (2)

ErikE
ErikE

Reputation: 50191

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;

Upvotes: 6

John Woo
John Woo

Reputation: 263683

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

SELECT convert(varchar, getdate(), 106)

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

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

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.

Upvotes: 6

Related Questions