1

I'm trying to execute the following stored procedure with a dynamic date parameter so that it picks up 6 months ago to yesterday.

When I run on a server with US language settings it works however when I run on a server with UK language settings I get the error:

Conversion failed when converting date and/or time from character string

I have tried converting the date to YYYYMMDD as this works on all servers however I have been unable to - I have even tried to force into a VARCHAR but no luck :(

Is there a way to get the date as YYYYMMDD format? Hope you can help. 

USE Reports

DECLARE @DteStart   DATETIME2(3)
DECLARE @DteEnd     DATETIME2(3)
SELECT  @DteStart = DATEADD(MM, DATEDIFF(MM, 0, CONVERT(DATE,GETDATE(),103))-6,0)
SELECT  @DteEnd   = DATEADD(DD, DATEDIFF(DD, 0, CONVERT(DATE,GETDATE(),103))-1,0)
EXEC [dbo].[spReportsMyData] @DteStart, @DteEnd;
glennsl
  • 28,186
  • 12
  • 57
  • 75
Baseline9
  • 101
  • 1
  • 3
  • 12
  • That code is product specific. Tag the dbms you're using. – jarlh Oct 06 '17 at 14:18
  • If you use the [`Date`](https://learn.microsoft.com/en-us/sql/t-sql/data-types/date-transact-sql) data type in all of the appropriate places there shouldn't be a problem. Fiddling with times you don't want and string representations is the way to madness. PSA: [ISO date format](https://xkcd.com/1179/). – HABO Oct 06 '17 at 15:16
  • I can't reproduce it? What `language` are exactly using? – gotqn Oct 08 '17 at 06:24
  • Its working now! I think our DBA's have been messing about with the language settings, all sorted, thanks for the responses :) – Baseline9 Oct 23 '17 at 09:07

1 Answers1

1

Try something like this:

Declare 
    @StartDate as Date
    , @EndDate as Date

Set @EndDate = DateAdd(Day, -1, Getdate())
Set @StartDate = DateAdd(Month, -6, @EndDate)

Select 
    Convert(VARCHAR(10), @StartDate, 112) As StartDate
    , CONVERT(VARCHAR(10), @EndDate, 112) As EndDate

Does your stored procedure seriously need the dates specifically in YYYYMMDD format, though?

Brian
  • 1,238
  • 2
  • 11
  • 17