CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2014
sukesh
sukesh

Reputation: 2423

Date conversion issue in SQL Server 2014

There is a column TriggerDate of type varchar(50). It stores values in MM/dd/yyyy format(eg: 05/21/2016).

There are 10 records in this table for the date May 21, 2016

When I run this query

select * 
from Analytics 
where convert(date, TriggerDate) = '2016-05-21'

I get the result in the results tab, but the messages tab displays this error

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

However, when I run

select top 10 * 
from Analytics 
where convert(date, TriggerDate) = '2016-05-21'

there are no errors

Executing select CONVERT(date,'05/21/2016') returns 2016-05-21 with no errors.

What baffles me is: how is top 10 making the difference and, it is working on my staging server also running SQL Server 2014.

How should I resolve this?

Using SQL Server 2014 in Windows Server 2012 R2 and the System Date is in the format 5/21/2016

Upvotes: 0

Views: 851

Answers (2)

Sandeep Kumar
Sandeep Kumar

Reputation: 1202

Use below query may be it helps you.

select * 
from Analytics 
where convert(varchar(10), cast(TriggerDate as date), 102) = '2016.05.21'

Upvotes: 0

Sunil Rajput
Sunil Rajput

Reputation: 980

SQL Server convert string to date

SELECT convert(datetime, '10/23/2016', 101) -- mm/dd/yyyy
SELECT convert(datetime, '2016.10.23', 102) -- yyyy.mm.dd ANSI date with century
SELECT convert(datetime, '23/10/2016', 103) -- dd/mm/yyyy
SELECT convert(datetime, '23.10.2016', 104) -- dd.mm.yyyy
SELECT convert(datetime, '23-10-2016', 105) -- dd-mm-yyyy

Try This

Upvotes: 3

Related Questions