Getting correct data from date entered

• gboehm

  Say Hey Kid

  Points: 695

  More actions

  January 28, 2008 at 9:15 am

  #772491

  Matt,

  This code will process under Preview, but no data shows up in the table.

  --clean up messy dates supplied

  DECLARE @enddate VARCHAR(MAX)

  select @enddate=

  case when convert(char,@enddate,108)='00:00:00'

  then dateadd(day,1,@enddate) --user didn't give us a time component

  else @enddate --user did give us a time component

  end

  --now used the cleaned up enddate

  SELECT

  substring(TAG , 10,50) as 'ALARM NAME',

  Case when value=0 then 'RESET' else 'ON' end as 'STATUS',

  CONVERT (CHAR(27),(TIME),109) as 'DATE & TIME'

  FROM dbo.OMRON_1_ALARMS

  where

  TAG like '%' + @EnterAlarm + '%'

  and TIME >=@StartDate

  and TIME<@enddate

  Is something wrong with the DECLARE statement?

  Thanks,

  Gary

• Matt Miller (4)

  SSC Guru

  Points: 124210

  More actions

  January 28, 2008 at 9:26 am

  #772501

  Re-read the previous post (edited) - I forgot you were doing this all in a report, and that reports prefer one single select statement with parameters....

  ----------------------------------------------------------------------------------
  Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

• gboehm

  Say Hey Kid

  Points: 695

  More actions

  January 28, 2008 at 10:13 am

  #772537

  Matt,

  Here's the latest code:

  --clean up messy dates supplied

  SELECT

  substring(TAG , 10,50) as 'ALARM NAME',

  Case when value=0 then 'RESET' else 'ON' end as 'STATUS',

  CONVERT (CHAR(27),(TIME),109) as 'DATE & TIME'

  FROM dbo.OMRON_1_ALARMS

  where

  TAG like '%' + @EnterAlarm + '%'

  and TIME >=@StartDate

  and TIME<case when convert(char,@enddate,108)='00:00:00'

  then dateadd(day,1,@enddate) --user didn't give us a time component)

  else @enddate --user did give us a time component

  end

  The problem is that now it ignores the time entered. I've played with the 108 and 109 numbers, I've tried entering 1/24/2008 11:00:00 and 1/24/2008 11 AM, I tried 108)<'23:59:59', just about everything I can think of, but nothing I've tried works for both scenarios, entering just a date or a date plus time. One combination I tried showed the correct times, but didn't report any date.

  I'm sorry to keep asking for help - if I'm too much, just tell me to buzz off.

  Thanks,

  Gary

• Matt Miller (4)

  SSC Guru

  Points: 124210

  More actions

  January 28, 2008 at 10:34 am

  #772549

  Open your report up, go to Report parameters, and make sure that Enddate is defined as a datetime parameter. If you have Enddate defined as a string - this all falls apart....

  ----------------------------------------------------------------------------------
  Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

• gboehm

  Say Hey Kid

  Points: 695

  More actions

  January 28, 2008 at 12:48 pm

  #772602

  Matt,

  That was it - I missed that completely. It works like a chemp now.

  Can't thank you enough - take last weekend off!

  Gary