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 |
|
midi25
Starting Member
24 Posts |
 Posted - 2005-05-22 : 14:48:29
|
| 1900-01-01 00:00:00.000I have created a column and set a default definition on it. When a Null value is encountered the column should be filled with the current system date and time. Instead I am getting the above value.I created the table in SQL Server Enterprise Manager and simply put GetDate() in the default option for the column. Please help. Thanks |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-05-22 : 15:08:12
|
Well, first of all, I think you're a little bit confused about what a default value does in SQL Server. Look at the following example in Query Analyzer:CREATE TABLE #defaulttest(col1 INT, col2 DATETIME DEFAULT(GETDATE()))INSERT #defaulttest(col1, col2) SELECT 1,NULLINSERT #defaulttest(col1) SELECT 2SELECT * FROM #defaulttestDROP TABLE #defaulttest Let me know what the resultset was for that query on your system.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
midi25
Starting Member
24 Posts |
Posted - 2005-05-22 : 16:16:27
|
| your example works and I get the correct date and time. But what I want to do is have the date and time inserted into the column. Here is how I am performing the insert eg insert into customers values ('jon','doe','jd@hotmail.com','voodoo','test','')The 6th field I have left empty. And the column is set to allow nulls. This then should have the system date and time inserted. But it dosnt seem to be working. |
|
|
|
midi25
Starting Member
24 Posts |
Posted - 2005-05-22 : 16:35:27
|
| Its ok I have done it. Thanks . I just used this insert into customers (firstname,lastname,email,password,secretanswer)values ('y','l','m','e','y')It seems to have worked in my table ok. I am new to SQL so expect the silly questions lol. Thanks |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-05-23 : 18:43:09
|
| The problem with your previous INSERT was that you were passing an empty string ('') which is not the same as inserting a NULL. The only way to insert NULL is to use the keyword NULL, like Derrick did. Or eliminate it from the INSERT statement all together, like your solution does.---------------------------EmeraldCityDomains.com |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-05-23 : 18:46:32
|
| And hey, as long as we're passing out tips, the tipoff about your date is that the datetime value "1900-01-01 00:00:00.000" is the equivalent of day 0.SELECT convert(datetime, 0)---------------------------EmeraldCityDomains.com |
|
|
|
Bad_Boy
Troll
6 Posts |
Posted - 2005-05-24 : 06:37:35
|
| GetDate().PleaseReturnCorrectValue() <--- add this to you SP |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2005-05-24 : 08:02:16
|
| You don't appear to be being very helpful Bad BoyDamianIta erat quando hic adveni. |
|
|
|
|
|
|
|
|
Why is GetDate returning this value?