Error in date format sql server 2012


I have an application whose information is stored in a database on sql server but I have the following problem:

When making a query with the following script directly from the sql server engine, it returns me records:


The FECHA_DESDE field is type DATETIME .

But, if the same query is made from the sql editor, it returns the following error, which is attached to the following image.

What can you do that for?

asked by E. Guerrero 05.02.2016 в 21:50

5 answers


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

answered by 05.02.2016 в 21:54

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:

  • Use the function CONVERT() indicated by @Elenasys in your answer
  • Use a date literal, that is, write the date in 'YYYYMMDD' or 'YYYY-MM-DD'
  • 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


    • 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
    answered by 05.02.2016 в 22:45

    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
    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
    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.


    answered by 04.05.2016 в 20:09

    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

    answered by 08.02.2016 в 05:08

    You can set the date as DMY

    SELECT * FROM PR_11 WHERE FECHA_DESDE > '28/07/2014' AND CODIGO='4891';
    answered by 04.05.2016 в 20:13