Datetime Data type problem - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Datetime Data type problem

Author

Topic

Sep410
Posting Yak Master

117 Posts

Posted - 2008-08-13 : 16:04:59

Hi all,
I am working in SQL 2005 and I have datetime data type in my table.
The problem is when user don’t enter any date it SQL will set a default value of 1/1/1900 12:00:00 AM
For the field (The field’s Allow null property is true.).
What should I do?
Thanks

Sep

medtech26
Posting Yak Master

169 Posts

Posted - 2008-08-13 : 16:15:04

setting GETDATE() as defualt should solve this issue.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-08-13 : 16:17:02

quote:
Originally posted by Sep410

What should I do?

In your Application don't initiate any insert or update to datetime-field in this case...

Webfred

Too Old to Rock 'n' Roll, Too Young to Die

Sep410
Posting Yak Master

117 Posts

Posted - 2008-08-13 : 16:17:58

No I don’t think so. If users don’t want to enter their date of birth it does not mean their birth date is today.

Sep

chrpeter
Starting Member

31 Posts

Posted - 2008-08-13 : 16:23:06

Can you post the insert statement?
I need a little more info to give any advice.

medtech26
Posting Yak Master

169 Posts

Posted - 2008-08-13 : 16:23:19

quote:
Originally posted by Sep410

No I don’t think so. If users don’t want to enter their date of birth it does not mean their birth date is today.

Sep

Sorry, I thought you meant something else.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-13 : 16:24:54

well, set the default on the table to NULL then. then that indicates that the user hasn't entered a date.

-------------
Charlie

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-13 : 16:30:25

No.

Have it allow nulls without a default. Then either exclude the column in your insert or pass a NULL to it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Sep410
Posting Yak Master

117 Posts

Posted - 2008-08-13 : 16:33:37

My default is Null.
insert into tbl_Case values('08-57','','','','','','','','','','','','','','','2')
SQL statment is in my Vb.net code.

Here is my vb.net code
Sql = "insert into tbl_Case values('" & txtCaseID.Text & "','" & cboIntakeWorker.SelectedValue & "','"
Sql &= "" & cboServiceArea.SelectedValue & "','" & cboProgram.SelectedValue & "','"
Sql &= "" & cboPriority.SelectedValue & "','" & cboZone.SelectedValue & "','"
Sql &= "" & cboReferral.SelectedValue & "','" & cboFamilyStatus.SelectedValue & "','"
Sql &= "" & System.DBNull.Value & "','"
Sql &= "" & txtOpenDate.Text & "','"
Sql &= "" & cboEAP.Text & "','" & txtEAPContact.Text & "','"
Sql &= "" & cboExperience.SelectedValue & "','" & cboReOpen.Text & "','"
Sql &= "" & cboFamilyIncome.SelectedValue & "','" & cboCaseStatus.SelectedValue & "')"

as you see there is no difference when I pass System.DBNull.Value
it will be '' in the sql statment which is passed to My database.

Sep

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-13 : 16:38:03

Yikes! Never concatenate your queries like this. You are vulnerable to SQL injection.

Something is wrong with your application code as how you have the table/column configured will work fine. You can even test it with pure T-SQL.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Sep410
Posting Yak Master

117 Posts

Posted - 2008-08-13 : 16:42:26

The way that I am coding is Company standard.I don't have any other choice.

Sep

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-13 : 16:43:22

Then you should educate them on SQL injection as that's really bad.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-13 : 16:47:48

quote:
Originally posted by tkizer

No.

Have it allow nulls without a default. Then either exclude the column in your insert or pass a NULL to it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Hi tkizer

Is there something wrong / dangerous with my suggestion of a default NULL value on that column?

-------------
Charlie

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-13 : 16:49:11

It's unnecessary.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Sep410
Posting Yak Master

117 Posts

Posted - 2008-08-13 : 17:02:25

Tara I did what you said and it is working well.
Thanks.

Sep

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-08-13 : 17:07:52

quote:
Originally posted by Sep410

Tara I did what you said and it is working well.
Thanks.

Sep

So please tell me your solution.
Is it:
exclude the column from insert?

Webfred

Too Old to Rock 'n' Roll, Too Young to Die

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-14 : 04:13:57

quote:
Originally posted by tkizer

It's unnecessary.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Thanks Tkizer. You are, of course, right. I didn't know you could miss out a column in an insert that didn't have a default like that.

-------------
Charlie

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-14 : 05:21:25

quote:
Originally posted by Sep410

My default is Null.
insert into tbl_Case values('08-57','','','','','','','','','','','','','','','2')
SQL statment is in my Vb.net code.

Here is my vb.net code
Sql = "insert into tbl_Case values('" & txtCaseID.Text & "','" & cboIntakeWorker.SelectedValue & "','"
Sql &= "" & cboServiceArea.SelectedValue & "','" & cboProgram.SelectedValue & "','"
Sql &= "" & cboPriority.SelectedValue & "','" & cboZone.SelectedValue & "','"
Sql &= "" & cboReferral.SelectedValue & "','" & cboFamilyStatus.SelectedValue & "','"
Sql &= "" & System.DBNull.Value & "','"
Sql &= "" & txtOpenDate.Text & "','"
Sql &= "" & cboEAP.Text & "','" & txtEAPContact.Text & "','"
Sql &= "" & cboExperience.SelectedValue & "','" & cboReOpen.Text & "','"
Sql &= "" & cboFamilyIncome.SelectedValue & "','" & cboCaseStatus.SelectedValue & "')"

as you see there is no difference when I pass System.DBNull.Value
it will be '' in the sql statment which is passed to My database.

Sep

'' and NULL are different

See what you understand

SELECT CAST('' as DATETIME), CAST(NULL as DATETIME)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources