Date out of range but only in stored procedure call

Question

I am trying to convert a DATETIME field to dd-mm-yyyy format in my query.

When I run either of the following lines in SSMS, the query executes successfully and I get my date in the correct format.

CONVERT(VARCHAR(30), CONVERT(DATETIME, f.Created, 101), 103) as [Created]
CONVERT(VARCHAR, f.created, 105) as [Created]

f.Created is a DATETIME column

However if I try to run it as part of a query within a stored procedure I get an error:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Within the stored procedure, I've tried setting the language to British and also the date format to dmy but I still get an out of range error. If I remove these lines or just select the f.Created field normally, it works.

What is going wrong?

Edit:

Query to run SP

DECLARE @html nvarchar(MAX);
EXEC spQueryToHtmlTable @html = @html OUTPUT,  @query = N'

 SELECT top 100 * from 
  (
      select 
      c.clno + ''.'' + f.fileno as [Number]
      ,c.clName as [Client Name]
      ,f.fileDesc as [File name]
      ,CONVERT(VARCHAR(255), f.created, 105) as Created_ddmmyyyy
      --Or either of these: 
      --CONVERT(VARCHAR(30), CONVERT(DATETIME, f.Created, 101), 103) as [Created]
      --CONVERT(VARCHAR, f.created, 105) as [Created]
      from config.dbfile f
      join config.dbclient c on c.clid = f.clid
  ) x
  where x.Department = ''Import'' and Type = ''Import''
  and 
x.Created_ddmmyyyy Between DATEADD(m, -2, GETDATE()) and GETDATE()

', @orderBy = '';

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Outlook',
    @recipients = 'email@email.com;',
    @subject = 'subject of email',
    @body = @html,
    @body_format = 'HTML',
    @query_no_truncate = 1,
    @attach_query_result_as_file = 0;  

SP:

/****** Object:  StoredProcedure [dbo].[spQueryToHtmlTable]    Script Date: 10/16/2017 11:47:40 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- Description: Turns a query into a formatted HTML table. Useful for emails. 
-- Any ORDER BY clause needs to be passed in the separate ORDER BY parameter.
-- =============================================
CREATE PROC [dbo].[spQueryToHtmlTable] 
(
  @query nvarchar(MAX), --A query to turn into HTML format. It should not include an ORDER BY clause.
  @orderBy nvarchar(MAX) = NULL, --An optional ORDER BY clause. It should contain the words 'ORDER BY'.
  @html nvarchar(MAX) = NULL OUTPUT --The HTML output of the procedure.
)
AS
BEGIN   
  SET NOCOUNT ON;

  IF @orderBy IS NULL BEGIN
    SET @orderBy = ''  
  END

  SET @orderBy = REPLACE(@orderBy, '''', '''''');

  DECLARE @realQuery nvarchar(MAX) = '
    DECLARE @headerRow nvarchar(MAX);
    DECLARE @cols nvarchar(MAX);    

    SELECT * INTO #dynSql FROM (' + @query + ') sub;

    SELECT @cols = COALESCE(@cols + '', '''''''', '', '''') + ''['' + name + ''] AS ''''td''''''
    FROM tempdb.sys.columns 
    WHERE object_id = object_id(''tempdb..#dynSql'')
    ORDER BY column_id;

    SET @cols = ''SET @html = CAST(( SELECT '' + @cols + '' FROM #dynSql ' + @orderBy + ' FOR XML PATH(''''tr''''), ELEMENTS XSINIL) AS nvarchar(max))''    

    EXEC sys.sp_executesql @cols, N''@html nvarchar(MAX) OUTPUT'', @html=@html OUTPUT

    SELECT @headerRow = COALESCE(@headerRow + '''', '''') + ''<th>'' + name + ''</th>'' 
    FROM tempdb.sys.columns 
    WHERE object_id = object_id(''tempdb..#dynSql'')
    ORDER BY column_id;

    SET @headerRow = ''<tr>'' + @headerRow + ''</tr>'';

    SET @html = ''<table border="1">'' + @headerRow + @html + ''</table>'';    
    ';

  EXEC sys.sp_executesql @realQuery, N'@html nvarchar(MAX) OUTPUT', @html=@html OUTPUT
END

GO

Typical data in f.created column

2002-11-05 00:00:00.000
2003-12-15 00:00:00.000
2002-11-05 00:00:00.000
2002-11-05 00:00:00.000
2002-11-06 00:00:00.000

Expected result for the f.Created column

05-11-2002
15-12-2003
05-11-2002
05-11-2002
06-11-2002

I would be happy with any variation of dd-mm-yy/yyyy

Answer 1

The error occurs in WHERE condition of the first query

WHERE
x.Created_ddmmyyyy Between DATEADD(m, -2, GETDATE()) and GETDATE()

because you have converted your DATETIME to string and then you trying to compare that string with two other dates (BETWEEN)

Just move this condition inside your sub-query and use original DATETIME column in WHERE, and converted in SELECT.. .

in fact why do you even use sub-query:

@query = N'
select top 100
  c.clno + ''.'' + f.fileno as [Number]
  ,c.clName as [Client Name]
  ,f.fileDesc as [File name]
  ,CONVERT(VARCHAR(255), f.created, 105) as Created
from config.dbfile f
join config.dbclient c on c.clid = f.clid
where Department = ''Import'' 
and Type = ''Import''
and f.Created Between DATEADD(m, -2, GETDATE()) and GETDATE()'

or have two columns - one original DATETIME to use in WHERE, other converted to VARCHAR to show to client.

Answer 2

Don't convert the f.created column at all if it is in datetime format already. dateadd() returns a datetime, exactly matching your column's type.

SELECT top 100 * from 
  (
      select 
      c.clno + '.' + f.fileno as [Number]
      ,c.clName as [Client Name]
      ,f.fileDesc as [File name]
      ,f.created
      --,CONVERT(VARCHAR(255), f.created, 105) as Created_ddmmyyyy
      from config.dbfile f
      join config.dbclient c on c.clid = f.clid
  ) x
  where x.Department = 'Import' and Type = 'Import'
  and x.Created >= DATEADD(m, -2, GETDATE())
  and x.Created < GETDATE()

The only reason I can think of why you've added the conversion, is because you've read somewhere that getdate() includes the time component and you want results returned for yesterday minus 2 months up until today, and not for "now" minus 2 months up until "now" (i.e. including time). If this is the case, you've been led astray by one of the many incorrect examples found in the internet. Forget about the conversion into strings, just use a smart combination of dateadd() and datediff() and you'll get exactly what you need without the international date format nightmare you're experiencing now and as an added bonus: it's faster. If you want to be fully informed, please read: Dwain Camps' blog post called "Manipulating Dates and Times in T-SQL". But here's a quick example that implements date truncation to get rid of the time component returned by getdate().

 SELECT top 100 * from 
  (
      select 
      c.clno + '.' + f.fileno as [Number]
      ,c.clName as [Client Name]
      ,f.fileDesc as [File name]
      ,f.created
      --,CONVERT(VARCHAR(255), f.created, 105) as Created_ddmmyyyy
      from config.dbfile f
      join config.dbclient c on c.clid = f.clid
  ) x
  where x.Department = 'Import' and Type = 'Import'
  and x.Created >= DATEADD(m, -2, dateadd(d, datediff(d, 0, GETDATE()), 0))
  and x.Created < dateadd(d, datediff(d, 0, GETDATE()), 0)

Please also note the use of >= and < instead of between-and in my examples. Between includes both the start and end values, so you could incorrectly include rows that have a time component of 00:00:00.000 if you use between instead of => and <.

To get a date string formatted such that it can never be interpreted incorrectly, format into the yyyy-mm-dd format using:

select convert(varchar(10), getdate(), 120)

As another plus, it even sorts correctly! Full information on convert and its formatting parameter can be found in SQL server books online: Books online

Answer 3

The correct way would simply be:

CONVERT(VARCHAR(255), f.created, 105) as [Created]

You should always include length with string types in SQL Server. However, this is not going to fix your problem. One issue may be the naming convention. I don't know what your stored procedure looks like, but you can try:

CONVERT(VARCHAR(255), f.created, 105) as Created_ddmmyyyy

That way, the two will not get confused. If Created_ddmmyyyy is then being used for some purpose, your query may be trying to convert it back to a date/time -- using a different format.

You should not ever get an out-of-range error converting a date/time to a string.