Assign empty string '' if datetime is null

_{Friday, August 15, 2008 2:31 AM | 1 vote}

hi all,

may i know how to assign '' (empty string) for a varible is null which type is datetime?

example:

select EnterTime from LoginTable

if the EnterTime is null, this query will return

NULL

but right now, i need to get ' ' not NULL.

any idea?

thanks 1st

_{Friday, August 15, 2008 4:42 AM ✅Answered | 1 vote}

This is a little tricky because of the nature of the beast, but very plausible to accomplish.  The first problem you may have faced is that the database engine automatically interprets a value of '' as an inplicit 0, which makes the optmizer believe you passed in the date 1/1/1900.  This is because 1/1/1900 is the first date SQL realizes.  You can do some conversions to see this be converting 1/1/1900 to an integer.

Code Snippet

declare @dt datetime

set @dt = '1/1/1900'

select convert(int,@dt) --0

Now your first thought in solving the problem is I will use a case or coalesce statement, right?  Well, you are partially right but there are some is another step that has to happen first.  You need to convert the datetime column to a string before you can supply a '' for null values because the datetime data type will take precedence in the case or coalesce statement and still supply the date 1/1/1900, as seen below.

Code Snippet

select COALESCE(EnterTime,''), case when EnterTime is null then '' else EnterTime end

from LoginTable

Now the correct solution.

Code Snippet

--121 convert formatting makes it look like a normal date/time returned value

select COALESCE(convert(varchar,EnterTime,121),''),

case when EnterTime is null then '' else convert(varchar,EnterTime,121) end

from LoginTable

_{Sunday, May 18, 2014 11:23 AM}

By default DateTime is not nullable because it is a Value Type, using the nullable operator introduced in C# 2, you can achieve this.

Using a question mark (?) after the type or using the generic style Nullable.

Nullable < DateTime > nullDateTime;

or

DateTime? nullDateTime = null;Full Source...C# Datetime Null

_{Sunday, May 18, 2014 12:23 PM}

You wont be able to achieve this unless you convert datatype to varchar or char. Reason is '' is not a valid datetime value. So '' will interpreted as value of 0 which corresponds to a datevalue of 19000101.

If this is for display purpose I would suggest keeping the datatype as is and store value as base date 19000101. Then in your presentation layer you can apply logic to replace the base date with blank. Doing data conversion just for this requirement in sql will mean the field losing its original datatype which will cause issues if these values are used for any further manipulation like comparison,sorting etc. So better to defer this until the presentation layer.

Please Mark This As Answer if it helps to solve the issue Visakh http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs