CODEWITHSUNDEEP
sqlbetween
Auzzzy
Auzzzy

Reputation: 69

Check for Date Between Dates w/ SQL

I'm getting an error when running this query:

SELECT '06/30/' + 
 CASE WHEN GETDATE() BETWEEN Cast('1/01/' + YEAR(GETDATE()) + ' 12:01:01' As DateTime) And Cast('6/30/' + YEAR(GETDATE()) + ' 12:01:01' as DateTime)
      THEN CAST(
           YEAR(DATEADD(YY, 2, GETDATE())) AS VARCHAR(4))
      WHEN GETDATE() BETWEEN Cast('7/01/' + YEAR(GETDATE()) + ' 12:01:01' As DateTime) And Cast('12/31/' + YEAR(GETDATE()) + ' 12:01:01' as DateTime)
      THEN CAST(
           YEAR(DATEADD(YY, 3, GETDATE())) AS VARCHAR(4))
      END
      AS DateEnding FOR XML RAW

Conversion failed when converting the varchar value '1/01/' to data type int.

Upvotes: 1

Views: 284

Answers (2)

Greg
Greg

Reputation: 3522

JamieSee is correct about casting it to VARCHAR(4). A better way would be to format using non-ambiguous dates. SQL will always work out 6/30/2012 as 30th June 2012, but is 1/7/2012 the 1st July (US Format) or 7th Jan (UK Format)?

SELECT '06/30/' + 
 CASE WHEN GETDATE() BETWEEN Cast(CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '-01-01 12:01:01' As DateTime) And Cast(CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '-06-30 12:01:01' as DateTime)
      THEN CAST(
           YEAR(DATEADD(YY, 2, GETDATE())) AS VARCHAR(4))
      WHEN GETDATE() BETWEEN Cast(CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '-07-01 12:01:01' As DateTime) And Cast(CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '-12-31 12:01:01' as DateTime)
      THEN CAST(
           YEAR(DATEADD(YY, 3, GETDATE())) AS VARCHAR(4))
      END
      AS DateEnding FOR XML RAW

Upvotes: 0

JamieSee
JamieSee

Reputation: 13020

You need to CAST or CONVERT your YEAR(GETDATE()) to varchar(4) when trying to combine it with text, otherwise, it attempts to convert the text to an int instead.

SELECT '06/30/' + 
 CASE WHEN GETDATE() BETWEEN Cast('1/01/' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) + ' 12:01:01' As DateTime) And Cast('6/30/' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) + ' 12:01:01' as DateTime)
      THEN CAST(
           YEAR(DATEADD(YY, 2, GETDATE())) AS VARCHAR(4))
      WHEN GETDATE() BETWEEN Cast('7/01/' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) + ' 12:01:01' As DateTime) And Cast('12/31/' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) + ' 12:01:01' as DateTime)
      THEN CAST(
           YEAR(DATEADD(YY, 3, GETDATE())) AS VARCHAR(4))
      END
      AS DateEnding FOR XML RAW

Upvotes: 2

Related Questions