Check for Null DateTime - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Check for Null DateTime

Author

Topic

itpriyesh88
Starting Member

13 Posts

Posted - 2012-09-09 : 13:27:57

I have Datetime field in my table which is set to NULL.
Now, when I am trying to get this NULL Value in my ASP.Net WebSite I am getting values like 01/01/0001.
How to get NULL values for this field in my ASP project??
Please help....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-09 : 13:29:28

whats the datatype of field?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

itpriyesh88
Starting Member

13 Posts

Posted - 2012-09-09 : 13:36:14

DateTime.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-09 : 14:47:38

are you trying to retrieve column as it is or using it an expression?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2012-09-09 : 18:17:46

An empty string is not a null value in SQL Server. It appears that you are using an empty string as null - and inserting that value. When you insert an emptry string it is evaluated as 0 for a datetime which is 1900-01-01.

Make sure you are actually using NULL's and not empty strings.

itpriyesh88
Starting Member

13 Posts

Posted - 2012-09-09 : 23:59:38

Yes, you are right I am inserting empty string as NULL and I need empty string while retrieving it from database instead of 1900-01-01. Is there any way to do it.??
Or tell me a way to insert empty string in a datetime field and and retrieving it as empty string in ASP.Net application.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-10 : 00:26:15

quote:
Originally posted by itpriyesh88

Yes, you are right I am inserting empty string as NULL and I need empty string while retrieving it from database instead of 1900-01-01. Is there any way to do it.??
Or tell me a way to insert empty string in a datetime field and and retrieving it as empty string in ASP.Net application.

why dont insert as NULL and then retrieve as ''?

ie something like

SELECT ISNULL(CAST(field AS varchar(10)),''),..
FROM table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-09-10 : 13:28:49

[code]DECLARE @Foo DATETIME = '1900-01-01T00:00:00.000';
--DECLARE @Foo DATETIME = '1900-01-02T00:00:00.000';

SELECT IIF(@Foo = '1900-01-01T00:00:00.000', '', CAST(@Foo AS VARCHAR(25)))[/code]

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2012-09-10 : 14:14:37

quote:

Originally posted by Lamprey

DECLARE @Foo DATETIME = '1900-01-01T00:00:00.000';
--DECLARE @Foo DATETIME = '1900-01-02T00:00:00.000';

SELECT IIF(@Foo = '1900-01-01T00:00:00.000', '', CAST(@Foo AS VARCHAR(25)))

SQL Server does not have IIF function. You would have to use CASE or COALESCE/NULLIF.

SELECT COALESCE(NULLIF(datefield, '1900-01-01'), '')

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-09-10 : 14:19:30

quote:
Originally posted by jeffw8713

quote:
Originally posted by Lamprey

DECLARE @Foo DATETIME = '1900-01-01T00:00:00.000';
--DECLARE @Foo DATETIME = '1900-01-02T00:00:00.000';

SELECT IIF(@Foo = '1900-01-01T00:00:00.000', '', CAST(@Foo AS VARCHAR(25)))

SQL Server does not have IIF function. You would have to use CASE or COALESCE/NULLIF.

SELECT COALESCE(NULLIF(datefield, '1900-01-01'), '')

FYI, SQL 2012 does.

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2012-09-11 : 14:46:23

Ah, didn't know that. Interesting...still won't use it though ;)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-09-13 : 02:33:16

quote:
Originally posted by itpriyesh88

Yes, you are right I am inserting empty string as NULL and I need empty string while retrieving it from database instead of 1900-01-01. Is there any way to do it.??
Or tell me a way to insert empty string in a datetime field and and retrieving it as empty string in ASP.Net application.

Just return actual DATETIME value to ASP.NET application and handle it when showing on the page.

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-09-13 : 02:34:34

Also you need to know why you should never pass empty string to non-character datatypes
http://beyondrelational.com/modules/2/blogs/70/posts/10841/empty-string-and-default-values.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources