query datetime column

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  August 10, 2011 at 12:47 pm

  #1366832

  shank-130731 (8/10/2011)

  ---

  My pain continues. If I have to change the WHERE clause if searching either year or month, I figured CASE in the WHERE clause would be the way to go. What I would do is add a Y (year) or M (month) on the incoming data. I get an error Incorrect syntax near >. Cause appears to be a conditional statement in a THEN clause. Is there a way around this?

  thanks!

  Yes, dynamic SQL.

  DECLARE @ETD varchar(20), @ETDvar datetime, @TimeData varchar(1)

  SET @ETD = '2010-01-01Y' --Last Year

  IF SubString(@ETD,11,1)='Y'

  SET @ETDvar = SubString(@ETD,1,10)

  SET @TimeData = SubString(@ETD,11,1)

  --Dynamic SQL

  DECLARE @sql VARCHAR(MAX)

  SELECT @sql = 'SELECT ETD FROM IXT_OceanShipments WHERE '

  + CASE @TimeData WHEN 'Y'

  THEN '(ETD >= @ETDvar) AND ETD < DATEADD(yy,1,@ETDvar)' --Year

  WHEN 'N'

  THEN '(ETD >= @ETDvar) AND ETD < DATEADD(mm,1,@ETDvar)' --Month

  END

  --PRINT @sql

  EXEC(@SQL)

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---