CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2
Shahul
Shahul

Reputation: 119

How Does date time column behave when column contains empty value?

I have encountered some problem , when i passed an empty value to date time variable it stores as default date(1900-01-01 00:00:00.000)

i wrote a conditional statement that 

declare @l_date datetime = '',@l_date1 datetime = getdate()

if @l_date <> ''
   select 'Yes' as '@l_date',@l_date D
else
   select 'No' as '@l_date',@l_date D

if @l_date1 <> ''
    select 'Yes' as '@l_date1',@l_date1 D
else
    select 'No' as '@l_date1',@l_date1 D

I expected both should be return "Yes". Unfortunately 1st condition returns "No" and 2nd condition returns "Yes". Can anyone explain the functionality how does it works.

Upvotes: 0

Views: 108

Answers (3)

Dan Guzman
Dan Guzman

Reputation: 46203

The documented default value is used for temporal types when an empty string is specified. Consider:

DECLARE
     @datetime datetime = ''
    ,@smalldatetime smalldatetime = ''
    ,@datetime2 datetime2 = ''
    ,@date date = ''
    ,@time time = ''
    ,@datetimeoffset datetimeoffset = ''
SELECT
     @datetime AS datetime
    ,@smalldatetime AS smalldatetime
    ,@datetime2 AS datetime2
    ,@date AS date
    ,@time AS time
    ,@datetimeoffset AS datetimeoffset;

Result:

+-------------------------+---------------------+-----------------------------+------------+------------------+------------------------------------+
|        datetime         |    smalldatetime    |          datetime2          |    date    |       time       |           datetimeoffset           |
+-------------------------+---------------------+-----------------------------+------------+------------------+------------------------------------+
| 1900-01-01 00:00:00.000 | 1900-01-01 00:00:00 | 1900-01-01 00:00:00.0000000 | 1900-01-01 | 00:00:00.0000000 | 1900-01-01 00:00:00.0000000 +00:00 |
+-------------------------+---------------------+-----------------------------+------------+------------------+------------------------------------+

Although zero is sometimes used instead of empty string to denote a default value, this is not allowed for the newer datetime2, date, time, and datetimeoffset types.

Upvotes: 2

Yabaz Thampi
Yabaz Thampi

Reputation: 82

You can try this


declare @l_date datetime=0 ,@l_date1 datetime = getdate()

if @l_date IS NOT NULL
   select 'Yes' as '@l_date',@l_date D
else
   select 'No' as '@l_date',@l_date D

if @l_date1 IS NOT NULL
    select 'Yes' as '@l_date1',@l_date1 D
else
    select 'No' as '@l_date1',@l_date1 D

Upvotes: 0

Thom A
Thom A

Reputation: 95561

There is no '' value for a datetime. '' will be implicitly cast the to the value 19000101, as you've seen. if you have an unknown date, use NULL (NULL does mean "unknown" after all). This changes your statement to:

DECLARE @l_date datetime, --I don't set the value as it'll already be NULL
        @l_date1 datetime = GETDATE();

IF @l_date IS NOT NULL 
    SELECT 'Yes' AS [@l_date],
           @l_date AS D;
ELSE
    SELECT 'No' AS [@l_date],
           @l_date AS D;

IF @l_date1 IS NOT NULL 
    SELECT 'Yes' AS [@l_date1],
           @l_date1 AS D;
ELSE
    SELECT 'No' AS [@l_date1],
           @l_date1 AS D;

(Note this still returns 'No' and 'Yes' respectively.)

Upvotes: 2

Related Questions