CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
FrenkyB
FrenkyB

Reputation: 7197

Sql server 2012 date comparison with format function not returning as expected

Data from my database:

enter image description here

As you can see I have several rows with column NEWS_DPU populated.

I don't understand why this query:

select * from canews
where format(news_dpu, 'mm.dd.yyyy') <= format(convert(datetime, '12.01.2016'), 'mm.dd.yyyy')

is returning only this:

enter image description here

Only one row is returned from query, but there should be several of them with NEWS_DPU smaller or equal to december 1st 2016. Am I missing something?

SOLUTION:

As John pointed out, MM means month and mm means minutes. I've just changed mm to MM and everything is fine.

Upvotes: 0

Views: 50

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81930

First change your format to MM which is month while mm is minutes

But I really think you want 

select * from canews
where news_dpu <= convert(datetime, '12.01.2016',103)

Upvotes: 4

Related Questions