Insert Null date - 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)

Insert Null date

Author

Topic

kprasadreddy
Starting Member

41 Posts

Posted - 2006-03-07 : 10:44:38

My code reads a txt file to insert data into a table and the date comes as 20050418

I wrote a funtion to convert the above to a date using the function below which works great but sometimes I get 00000000 as the date field and I need to insert null

sql statement

sSqlHrs = "Insert into tblIBMCSREstHours(CSRNumber,Estnumber,EstHours,EstAppDate) Values('" & CSRNumber "1," & fieldlist(49) & ",'" & Converttodate(fieldlist(48)) & "')"

When ever I get 00000000 my sql statement throws a Data mismatch error.Please advise....

Function Converttodate(givendate)
'convert date from 20051202 to 12/02/2005
Dim intDay As Byte
Dim intmonth As Byte
Dim intYear As Integer
intDay = Mid(givendate, 7, 2)
intmonth = Mid(givendate, 5, 2)
intYear = Mid(givendate, 1, 4)
Converttodate = intmonth & "/" & intDay & "/" & intYear
End Function

karuna
Aged Yak Warrior

582 Posts

Posted - 2006-03-07 : 10:50:31

Whats the error you are getting?

Thanks

Karunakaran

kprasadreddy
Starting Member

41 Posts

Posted - 2006-03-07 : 10:59:54

Data Type mismatch in Criteria selection

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-07 : 11:35:44

#1 -- You should always use parameters, never concatenate together sql statements like this.

#2 -- you shoudl always explcitly state data types in your VB functions and arguments. If ConvertToDate returns a string, declare that. If it returns a dateTime, declare it.

However, if you want to keep using your existing methods, you should remove the delimiting quotes in your sSQLHrs statement and let the function return either a date with quotes, or the word Null.

i.e., remove the single quotes around your date function here:

sSqlHrs = "Insert into tblIBMCSREstHours(CSRNumber,Estnumber,EstHours,EstAppDate) Values('" & CSRNumber "1," & fieldlist(49) & "," & Converttodate(fieldlist(48)) & ")"

and then change your function like this:


Function Converttodate(givendate as string) as string
  'convert date from 20051202 to 12/02/2005
  Dim intDay As Byte
  Dim intmonth As Byte
  Dim intYear As Integer

  if GivenDate = "00000000" then
    ConvertToDate = "Null"
  else
    intDay = Mid(givendate, 7, 2)
    intmonth = Mid(givendate, 5, 2)
    intYear = Mid(givendate, 1, 4)
    Converttodate = "'" & intmonth & "/" & intDay & "/" & intYear & "'"
  end if
End Function

But please consider using sql commands and parameters to avoid any formatting issues like this.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-08 : 01:25:26

>>'convert date from 20051202 to 12/02/2005

Note that if your server is set to dmy format, you will get error for dates like 12/18/2005
It is better to use yyyy-mm-dd format like 2005-12-02 .

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-03-08 : 04:17:18

"It is better to use yyyy-mm-dd format like 2005-12-02 "

It is better to use yyyymmdd like 20051202 - no hyphens!!

"sometimes I get 00000000 as the date field and I need to insert null"

You can use NullIf(MyValue, ValueToMakeNull) at the SQL end, here's a test:


SELECT NullIf('00000000', '00000000'),
       NullIf('20051202', '00000000')
gives:
-------- -------- 
NULL     20051202

so
... & ",'" & Converttodate(fieldlist(48)) & "')"
would become
& ",NullIf('" & Converttodate(fieldlist(48)) & "', '00000000'))"

jsmith8858's caveats about using parameters still apply though!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources