October 4, 2002 at 11:52 pm
Say I have a table Job with columns Start and End (both datetime). For the job that is still going on, the end is set at '12/30/5000' ( I do not use NULL since I use the field in a <>= comparisons). The problem is, if I want to retrieve the record for report, I want the end date to be blanks[/b] instead of '12/30/5000' for the unfinished jobs. How do I do this?
"The way to get things done is not to mind who gets the credit for doing them." - Benjamin Howett
October 5, 2002 at 2:35 am
You cannot store 'blank' in a datetime column. Your best bet is to store it as null or 1/1/1900.
October 5, 2002 at 11:10 am
Use a case statement in your query
CASE ColEnd WHEN '12/30/5000' THEN '' ELSE CONVERT(VARCHAR,ColEnd,101) END AS ColEnd
Then if the values is 12/30/5000 you will see nothing otherwise the date outputs.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 6, 2002 at 1:52 am
Antares686, thank you very much.
"The way to get things done is not to mind who gets the credit for doing them." - Benjamin Howett
October 29, 2002 at 10:36 am
>> Say I have a table Jobs with columns start_date and end_date. For the job that is still going on, the end is set at '12/30/5000' (I do not use NULL since I use the field [sic] in a <>= comparisons). <<
Use the NULL as a token meaning "not finished", or "eternity" and code for it, thus:
COALESCE (CURRENT_TIMESTAMP, end_date)
NULLs are one of the many ways that columns are not like fields. The reason you thought of using a blank for a date is that you don't understand the differences yet. A column has a datatype; a field gets all its meaning from the program that reads it. We used to leave the fields in punch cards empty so we could fill them in later. You are still thinking that way.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply