Testing for null datetime
robert crossley
Hi,
I am having trouble comparing date time fields in an SQL query.
I have one table (T_HARV_OPS ) that has a date time field D_START_DATE which the values are null.
I have another table (TmpUpdateDetails ) that has date time field MIN_DELIV_DATE which has valid dates in it.
AND Year(TmpUpdateDetails.MIN_DELIV_DATE) > 0
AND TmpUpdateDetails.MIN_DELIV_DATE <> T_HARV_OPS.D_START_DATE
I want to update T_HARV_OPS with the data from TmpUpdateDetails, but only the records that are different and only when the TmpUpdateDetails has a valid date.
So I am looking for tests to do this.
In the end, to test if it was a valid datetime, I used a Year(field) > 0 to see if it had a valid value.
But I am having trouble with detecting if the values are different when one field is null. The statement TmpUpdateDetails.MIN_DELIV_DATE <> T_HARV_OPS.D_START_DATE does not find records when one of the fields is null. If I change one of the null records to a valid date that is different to the other, it returns that record as expected.
So are there any basic rules on SQL syntax when comparing datetime fields that are worthwhile documenting? Or perhaps functions like datediff?
I want to be able to:
· find null records,
· to identify when dates are different, and it should return true if one of the values is a null
· find dates within a day of each other (I can do this one by adding an integer of 1000000000 (or something) to the datetime I am comparing)
TIA
Rob.
-------------------------------------------
Most probably sent from my Dell Laptop.
Robert Crossley
Managing Director
Agtrix P/L Australia
Far Southern Queensland Office:
Unit 6, 2 Bonanza Drive
Billinudgel NSW 2483
AUSTRALIA
Postal:
PO Box 63
New Brighton 2483
P: 61 (0) 2 6680 1309
M: 61 (0)419 718 642
E: rob...@agtrix.com
W: www.agtrix.com
S: robertcrossley
28°30'14.81"S
153°31'41.79"E
Brisbane Office:
109 Milsom St
Cooparoo 4151
Queensland
P: 61 7 3843 3363
Data Directions
Robert,
I think that if you see a “Null Value” (ie. No data in a cell) it is because that column is a character field.
Now I’m just grasping at straws, but it sounds like DateTime is considered a numerical value. Thus to do your comparison, you may have to convert D_Start_Date to a numerical value using the expression val(D_Start_Date)
This would obviously assign it a “zero” value (for Null entries) and things might proceed from there when you run the query and update.
Regards,
Bill
--
--
You received this message because you are subscribed to the
Google Groups "MapInfo-L" group.To post a message to this group, send
email to mapi...@googlegroups.com
To unsubscribe from this group, go to:
http://groups.google.com/group/mapinfo-l/subscribe?hl=en
For more options, information and links to MapInfo resources (searching
archives, feature requests, to visit our Wiki, visit the Welcome page at
http://groups.google.com/group/mapinfo-l?hl=en
---
You received this message because you are subscribed to the Google Groups "MapInfo-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mapinfo-l+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
Peter Horsbøll Møller
--
Robert Crossley
Hi Peter,
I only just noticed that I hadn’t sent this, so will now for sake of completeness.
I am working in MapInfo, so IsNull is not an option.
I am really trying to understand how is the best way to handle datetime values.
They are supposed to be stored as a 17 digit number YYYYMMDDHHMMSSFFF.
Val() doesn’t work, but I haven’t really got my head around what nulls return in the various functions, nor if there is a proper way I should be dealing with comparisons etc.
I ended up writing a function with a some conditional statements, but it seems that there should be some corporate knowledge around on how best to approach this. Also this will only work in a MapBasic application, but I can at least use it in a select statement:
Select * From Table Where fDateTimeIsSame(DateTimeFld1, DateTimeFld2) = FALSE
Code for this function is provided below.
Obviously, failing to detect that a null value is different to a real value using a <> knocks my confidence in being able to account for all possibilities.
Function fDateTimeIsSame(ByVal dtDateTimeVal1 As DateTime, ByVal dtDateTimeVal2 As DateTime) As Logical
'-----------------------
'Subroutine: DisplayErrors
'Called By: Menu
'Description:
'Written By: RWC
'-----------------------
'Set up general error handler
ONERROR GOTO ErrorHandler
fDateTimeIsSame = FALSE
If Str$(dtDateTimeVal1) = "" And Str$(dtDateTimeVal2) <> "" Then
fDateTimeIsSame = FALSE
Exit Function
ElseIf Str$(dtDateTimeVal2) = "" And Str$(dtDateTimeVal1) <> "" Then
fDateTimeIsSame = FALSE
Exit Function
ElseIf dtDateTimeVal2 = dtDateTimeVal1 Then
fDateTimeIsSame = TRUE
Exit Function
End If
Exit Function
ErrorHandler:
CALL ErrorMessage("fDateTimeIsSame")
End Function
Lars I. Nielsen (GisPro)
I did a fast test on a MapInfo table with a datetime column. I added one row without entering values, and queried the field value in this row.
Hour(c_datetime) returns -1, which is an invalid result (should of course be 0-23), so could this be used to test for NULL ?
The same works if the table originates from Sql Server, and has a NULL datetime value.
Best regards / Med venlig hilsen
Lars I. Nielsen
GIS & DB Integrator
GisPro
