Error in date format sql server 2012

Use the CONVERT ()

CONVERT(datetime, @TU_VALOR , 103)

In your case you could get the value DateTime with the format dd/mm/yyyy based on the string '28/07/2014'

DECLARE @miFecha char(10) = '28/07/2014'
SELECT CONVERT(datetime,LEFT(@miFecha,2)+'/'+SUBSTRING(@miFecha,4,2)+'/'+RIGHT(@miFecha,4),103)

Result: 2014-07-28 00:00:00.000

Based on this your script would look like:

DECLARE @miFecha char(10) = '28/07/2014'
SELECT * FROM PR_11 WHERE FECHA_DESDE > CONVERT(datetime,LEFT(@miFecha,2)+'/'+SUBSTRING(@miFecha,4,2)+'/'+RIGHT(@miFecha,4),103) AND CODIGO='4891';

The format to use would be the 103 ( dd/mm/yyyy ) as specified in Date and Time Styles

The problem occurs because the conversion of a text string into a date depends on the configuration of the language of the connection.

To avoid this problem you can do two things:

Applying the latter, your query would be:

SELECT * FROM PR_11 WHERE FECHA_DESDE > '20140728' AND CODIGO='4891'; 

This article " Bad habits to kick: mis-handling date / range queries " is very interesting and explains this problem

Personally:

• When I'm launching queries to test or extract certain data I use literals because it's more convenient
• If I have to use a date in a specific format , I use CONVERT
• When I have to launch queries from code (in my case C # and .NET) use parameterized queries to make it the access layer to the data that does the conversion

Although this question has already been answered, I want to make my contribution.

Sometimes queries with dates are usually complicated since it depends on the language, to avoid this type of inconvenience it is advisable to use a standard format in the query, I recommend using the format YYYYMMDD that is achieved using a convert in this way :

select convert(nvarchar, getdate(), 112)

Your query would look like this:

SELECT * 
FROM PR_11 
WHERE convert(nvarchar, FECHA_DESDE, 112) > '20140728' AND CODIGO='4891'

Now, it seems tedious to convert the dates to YYYYMMDD format but the savings in headaches is important, I have been using this method for more than 12 years and I must say that I have never had any problems due to the regional configuration.

Another example assuming that I have a stored procedure that receives a datetime or smalldatetime, it would have to be converted to the format before said.

CREATE PROCEDURE usp_devuelve_listado_fecha_mayor 
        @fecha datetime,
        @codigo int
as
SELECT * 
FROM PR_11 
WHERE       convert(nvarchar, FECHA_DESDE, 112) >  convert(nvarchar, @fecha, 112)
        AND CODIGO = @codigo

A last option is to convert the date to the YYYYMMDD format from the application and send the string to the stored procedure.

CREATE PROCEDURE usp_devuelve_listado_fecha_mayor 
        @fecha nvarchar(8),
        @codigo int
as
SELECT * 
FROM PR_11 
WHERE       convert(nvarchar, FECHA_DESDE, 112) >  @fecha
        AND CODIGO = @codigo

I hope it can be useful as it served me for so long.

PS: For the sake of simplicity we used Select * PLEASE in real queries only use the necessary fields.

Greetings.

My friend as simple as specifying the date in this way '2014-07-28' you do not have to make conversions, you can save time and code in this way

You can set the date as DMY

SET DATEFORMAT DMY
SELECT * FROM PR_11 WHERE FECHA_DESDE > '28/07/2014' AND CODIGO='4891';