nothing returning for date

• Ed B

  Hall of Fame

  Points: 3130

  More actions

  August 16, 2011 at 10:25 am

  #1369185

  Sorry if this is a repeat.

  01/04/2004 is a numeric expression.

  SELECT 01/04/2004 -- 0

  SELECT 01.0/04/2004 -- 0.00012475049

  Datetime zero = "1900-01-01 00:00:00.000"

  SELECT CONVERT(DATETIME,(SELECT 01/04/2004))

  [1900-01-01 00:00:00.000]

  This may be why the original query returned no rows, because both values were implicitly converted to 1/1/1900.

• Jeff Moden

  SSC Guru

  Points: 1000539

  More actions

  August 16, 2011 at 11:15 pm

  #1369409

  spin (8/15/2011)

  ---

  hi

  the reason i wanted this...

  select * from Sales.SalesOrderHeader

  where OrderDate between 01/01/2004 and 31/12/2004

  ...is because i'm trying out SSRS using BIDS. i wanted the date to be a parameter value from a textbox/date picker within the report. when i try to run the report the using @startDate/@endDate the parameter pop up window appears and i type in 01/01/2004 to whatever and it returns nothing. it now works with the xxxx-xx-xx format but no one will want to type the date in that format.

  Try the following and see why that can never be...

  SELECT 01/01/2004, 31/12/2004

  You're not using dates... you're using quotients. 😉 Each "date" you have posted is actually a double division integer problem.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
  "Change is inevitable... change for the better is not".
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  Intro to Tally Tables and Functions