• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:



I have a field named dDate which has a datetime datatype in my table. How am i gonna insert an empty value (not null) in this field. Any function to do this? Tnx


4 Solutions
there is no way to do that. it must be null or a valid date.
if you must have an empty value you will need to change the data type to a char or varchar then use ''. if you do this you will loose the ability to compare dates in that field efficiently. I would leave it as a datetime data type and just allow nulls, put a null in the field.
You can insert a value 0 that will be internally converted into a date by SQL. However, I am not sure whether you can read it as zero because SQL will return a value like 01-01-1900 or something.
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

If IsEmpty(dDate) Then
    myField= vbNull
    myField= dDate
End If
I absolutely agree with wraith. Let the field accept Null values. Besides you can't get any emptier than Null.

Sethi's suggestion can work too. He's correct too that 0 gives a 01-01-1900 datetime value. Next time you do a SELECT, all with dates 01-01-1900 are to be considred empty which means probably additional code for checking.

Now the other DUDE's suggestion makes no sense at all.
zack_onlineAuthor Commented:
Thanks guys.  Expert always gimme incredible and unexpected answer that's why i tried asking this kind of question even though i tried it to myself. I juss wanna clear my doubt regarding this from u guys.

Field1 varchar(50)

Field1 = null = 50
Field1 = empty = 1
Is it true that if Field1 is null it consume the max 50 and if it is an empty string only 1 byte?

- zack
>>> Is it true that if Field1 is null it consume the max 50 and if it is an empty string only 1 byte? <<<

Not true for SQL Server... unsure about other db's, though.

In SQL Server, nullable columns have a "null map" for each row that requires, I thnik, 1 bit per nullable column.  For each nullable column, its corresponding bit in the map is either on or off to indicate whether that column does or does not contain real data.

Depending on a configuration option in SQL Server (ANSI_PADDING), varchar columns may or may not be expanded to their fully defined size when only partially occupied... this has to do with whether trailing spaces are automatically truncated or added.

If a varchar column is used in an index expression, then for that index (and the space it requires) the column is considered to be fully expanded with trailing spaces (in the index area only).

The book, Inside SQL Server by Kalen Delaney, explains this (and dozens of other intricacies about the internals of SQL Server) in extreme detail if you're interested.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now