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


Who is Participating?
SethiConnect With a Mentor Commented:
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.
there is no way to do that. it must be null or a valid date.
wraith821Connect With a Mentor Commented:
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.
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

If IsEmpty(dDate) Then
    myField= vbNull
    myField= dDate
End If
PePiConnect With a Mentor Commented:
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
JesterTooConnect With a Mentor Commented:
>>> 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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.