ASP writing datetime to MS SQL (used to write to Access)

We have just begun the process of converting the datbase from Access 2000 to MS SQL Server 2000.

I want to be able to write a null value to a datetime field(column); but I always get 1/1/1900 for a value.

I want to be able to write a time value to a datetime field(column); but it is always prefixed with 1/1/1900.

Am I asking/attempting the impossible?!?

TIA,
Chuck

For the first NULL date value, you should be able to set the colum to 'NULL' or ''.

For the second, I belive the DateTime field will always have a date; you will just have to evaluate the time component on it's own using SQL Server's Date functions (i.e. DATEPART)

Is there a specfic reason that you need just a time component?

Regarding the field where I want a <null> date: It is set to accept Nulls; and I'm writing only a pair of single quotes... but 1/1/1900 appears/is stored.

Regarding the 'time-only' field, in Access, it was just that (i.e. 8:00:00 AM); and it appears in the Employees record, once as 'Start Time' and again as 'Stop Time' (i.e. as empTimeStart, empTimeStop respectively). I can live with '1/1/1900 8:00:00 AM' but I have a bit of code to modify...and 'sides, it's not as aesethically pleasing... a bit of overkill where just a time value alone would suffice.

Are you able to write null to a datetime field?

Thanx for your rapid reply!!!

working on spelling aesthetically...

~grin~

You should NEVER allow NULL values in SQL Server, it can seriously hinder query performance.
You should always set a default value on all of your columns.

Granting that performance can take a hit, if NULLs are allowed, what would you recommend for a field where no value (i.e. <Null>) is more common than not... say a datetime field like Termination Date (empDateTerm)?

I really don't want a Date (datetime) of any sort/kind stored therein, as long as the employee is still on the payroll.

You suggest *NEVER*, but are there times when one should 'Never say never'?

I really don't know... I'm a nube, in transistion, from Access.

Best Practices is as follows

Default values for data types:

any text (varchar, char, etc...) ('')
int (-1) or (0)
any decimal (decimal, money, etc...) (0.00)
datetime (1/1/1900)

Memnoch,

Beaucoup thanx for your time & consideration. I read your 'Sticky Post' about MS SQL & NULLS.

Got a bunch o' ASP to tweak...