Convert Date Against Where Clause

I have Following dummy table with data:

ACID        srno    date(mm/dd/yyyy)    name
3            1     04/12/2010          mahesh
3            2     04/12/2010          mahendra

Now if I try with Following SQL Transact:

select srno from dummy
where name = 'mahesh'
and date= convert(datetime,'12/04/2010',101) –- I have date in dd/MM/yyyy Format
and ACID=3

It’s Not returning the srno of the table. That means Date is not execute convert s开发者_JAVA百科tatement as above What’s the reason?

Try using style 103 instead of 101.

select srno from dummy
where name = 'mahesh'
and date= convert(datetime,'12/04/2010',103) –- I have date in dd/MM/yyyy Format
and ACID=3

If you convert 12/04/2010 using format 101, you get date "December 4, 2010", which is not in your database. Use format 103 to convert a date in format dd/mm/yyyy to DateTime.

The database stores dates using the DateTime type which is format-agnostic. It does have a default format for string conversions, which seems to be mm/dd/yyyy (101) on your database.

However, when you convert a string to add it to your table, you want to specify the format of your input string, in your example dd/mm/yyyy (103).

Take a look at the MSDN article for CAST and CONVERT which details all format styles that you can use with dates.

To be honest, if you want to specify a DATE LITERAL in SQL Server, please stick with the simplest YYYYMMDD format, e.g.

and dummy.date = '20100412'

It is robust and works for all regional, user language and dateformat settings. This assumes the other side of the comparison is already a date column. Even if you had to CAST it, using this format you don't need to specify a format

and dummy.date = cast('20100412' as datetime)