SET dateTime variable to '' question

• FreeHansje

  SSChampion

  Points: 11751

  More actions

  November 30, 2007 at 9:04 am

  #177087

  So I was playing with the ISDATE function and scripted this:

  DECLARE@Datum2 datetime

  SET @Datum2 = ''

  SELECT ISDATE(@Datum2)

  SELECT @Datum2

  To my amazement a date is returned: 1900-01-01 00:00:00.000

  And thus the ISDATE returns a valid date, which means problems for me, but that's an aside. Is there a reason I should be aware of for this behaviour?

  Greetz,
  Hans Brouwer

• Ray M

  SSC-Insane

  Points: 21093

  More actions

  November 30, 2007 at 9:44 am

  #756035

  a little odd, but since you declared your variable a datetime, and initialized it by setting it to '' it looks like sql server set it to 0, or the first date a datetime can be.

  the isdate function will always return true on a variable declared as a datetime (Unless its null).

  the isdate function is more usefull on a parameter, or string that is not declared a datetime

  declare @myVar varchar(100)

  -- @Myvar is NULL

  select isdate(@Myvar)

  -- Initialized

  set @myVar = ''

  select isdate(@Myvar)

  --Result

  --0

  -- Set to a real date

  set @myvar='11/30/2007'

  select isdate(@Myvar)

  --Result

  --1

  set @myvar='Not a Date'

  select isdate(@Myvar)

  --Result

  --0

• Jeff Moden

  SSC Guru

  Points: 1004641

  More actions

  November 30, 2007 at 11:59 am

  #756095

  Not odd behaviour... same as what happens with most numeric datatypes...

  DECLARE @Datum2 INT

  SET @Datum2 = ''

  SELECT @Datum2

  --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)