datetime Datatype 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 2000 Forums

SQL Server Development (2000)

datetime Datatype Problem

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-17 : 11:05:56

Elway writes "I am trying to insert a date into a table in my database from a form. I am using sql server 7.0. The problem is that when the date goes into the table it always changes to the date 1/1/1900. The datatype in my form is date. But when i enter the date into the database directly and then view it on the form it is the correct date. Am i using the wrong datatype in my database?"

Lazer
Starting Member

8 Posts

Posted - 2002-01-17 : 12:06:39

What front end do you use?

quote:

Elway writes "I am trying to insert a date into a table in my database from a form. I am using sql server 7.0. The problem is that when the date goes into the table it always changes to the date 1/1/1900. The datatype in my form is date. But when i enter the date into the database directly and then view it on the form it is the correct date. Am i using the wrong datatype in my database?"

Elway
Starting Member

2 Posts

Posted - 2002-01-17 : 15:44:20

I am using VB 6.0 as my front-end.
Here is what is happening.
When i have a valid date in the text box it inserts fine into the database but when i have a blank textbox (the value is "" or null) the date in the database becomes "1/1/1900". In my sql statement in order to insert the date into SQL server i have to put quotes around the date. This works great except when there is no date which means the value goes into the database as "" which in turn gets changed to "1/1/1900". What i need to do is to insert the word Null into the database without the quotes around it. Any suggestions to do this?

Thanks
Elway

sica
Posting Yak Master

143 Posts

Posted - 2002-01-17 : 17:30:15

I think a CASE statement would do the thing.You could post your sql statement if you want more specific syntax help or check BOL.

Sica

Edited by - sica on 01/17/2002 17:30:40

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-01-17 : 17:55:37

Elway

I assume you know how to execute an SQL string from VB 6? - so are you using an "INSERT" SQL string?

eg
say you have vars x,y, and d (date)
strSQL = "INSERT INTO tTable (col1, col2, col3) VALUES (" & x & "," & y & "," & d & ")"
etc

Now, to control the possibility of NULLs, what I have is a simple function that I use to help my string manips.

Public Function fnSql(strVal as String) as String
If strVal = "" then
fnSql = "NULL"
Else
fnSql = "'" & strVal & "'"
End If
End Function

So then I build my string as follows:
strSQL = "INSERT INTO tTABLE (col1, col2, col3)"
strSQL = strSQL & " VALUES ("
strSQL = strSQL & fnSql(x) & ","
strSQL = strSQL & fnSql(y) & ","
strSQL = strSQL & fnSql(d) & ")"

Then if your date is a NULL, your strSQL should be
"INSERT INTO tTABLE (col1, col2, col3) VALUES ('bla','bla',NULL)"

I'm not really sure if this is answering your question, so let me know if it's not. I've done the code a little simpler than in reality to try and explain how it's done. Personally, I have fnSql handle different datatypes (especially dates) differently, using the Format() function, to make sure that date values will go into SQL the same way...

Hope this helps

Edited by - rrb on 01/17/2002 17:57:35

Edited by - rrb on 01/17/2002 17:59:21

Elway
Starting Member

2 Posts

Posted - 2002-01-18 : 08:56:22

RRB,

Thanks for the help.
It work great! Thanks a lot!

Elway

Edited by - elway on 01/21/2002 10:15:42

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources