I am needing to alter the time piece of a datetime field. I tried to use this syntax, but it gives me an error of

Incorrect syntax near the keyword as

What syntax can I use to alter the end time of my datetime field?

case when CAST([pcpts].stopit As DateTime)+CAST(case when CAST([pcpts].stopit As Time) > '11:00:00.000' then '11:00:00.000' ELSE CAST([pcpts].stopit As Time) end as datetime) As [SI]

What I am after is if the time value for [pcpts].stopit is > 11:00:00.000 I want my query to show 11:00:00.000,

if the time value for [pcpts].stopit is < 11:00:00.000 then show [pcpts].stopit

Examples would be: [pcpts].stopit = '01/01/2014 10:00:00.000'
query output should show '01/01/2014 10:00:00.000'

[pcpts].stopit = '01/01/2014 16:00:00.000'
query output should show '01/01/2014 11:00:00.000'

  • And what if the time is 16:47:32.765? Still output should be 11 am sharp? Feb 15 '16 at 19:41
  • Correct! Anything > 11 a.m. should be displayed as 11 a.m. Feb 15 '16 at 19:43

First thing is you will need to stop performing shorthand math operations like + against date/time values. This breaks when you switch to the newer types, which you should do.

For this particular problem, you can simply use a CASE expression to either (a) return the datetime as is, or (b) convert to date and then add 11 hours.

  THEN col
  ELSE DATEADD(HOUR, 11, CONVERT(datetime, CONVERT(date, col)))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.