Convert date versus Where clause

Question

I have a dummy data 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 to execute the 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

Does not return srno tables. This means that Date does not perform the conversion as described above. What is the reason?

Answer 1

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

Answer 2

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

DateTime, -. , , , mm/dd/yyyy (101) .

, , , dd/mm/yyyy (103).

MSDN CAST CONVERT, , .

Answer 3

, DATE LITERAL SQL Server, , YYYYMMDD, .

and dummy.date = '20100412'

, dateformat. , . CAST , ,

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