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.
| Author |
Topic |
|
capella07
Starting Member
46 Posts |
 Posted - 2008-09-26 : 13:14:52
|
I have a table with one of the columns (BirthDate) set to smalldatetime datatype. When I save to it, if there is no value coming in for that variable, I want it to be a NULL, rather than the date default of "1900-01-01 00:00:00".I.e. I have a stored procedure that builds an INSERT from a string in XML format. The SP takes in, for instance name and birthdate. It builds an INSERT statement with that input. So if the name 'John', but nothing comes in for the birthdate, the INSERT would look like: INSERT INTO personnel (name, birthdate) = ('John', 'Jan 1 1900 12:00AM')I hope that makes sense.How can I have it default to NULL rather than that default date?Thanks!=====================================f u cn rd ths, u cn gt a gd jb n prgrmng |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-26 : 13:21:37
|
either useINSERT INTO personnel (name, birthdate) = ('John',NULL)or set column as nullable and do not pass value at allINSERT INTO personnel (name) = ('John') |
 |
|
|
capella07
Starting Member
46 Posts |
Posted - 2008-09-26 : 14:00:45
|
| Thanks for the suggestion, visakh. After I posted my question I found the fix. The SELECT pulling the data from the XML string reads each field like such: "@BirthDate = ISNULL(BirthDate, '')", so the empty string is where the default datetime was coming from. I simply changed that to "@BirthDate = BirthDate" and I got a nice NULL in the date field after an insert!Thanks again.=====================================f u cn rd ths, u cn gt a gd jb n prgrmng |
|
|
|
|
|
|
|
|
How do I save NULL into a smalldatetime field