[thelist] ASP - SQL Date Time Help

Matt Warden mwarden at gmail.com
Wed Aug 4 12:11:51 CDT 2004


On Wed, 04 Aug 2004 11:50:32 -0400, Michael Pack <michaelpack at wvdhhr.org> wrote:
> Hi all, I'm having a problem inserting an empty value into a datetime
> field in SQL Server 2000 database. The field is not required in the form
> so I must allow for NULL.
> 
> I am concatenating my string as follows
> 
> if request.Form("income_no_end_month") = "" &
> request.Form("income_no_end_day") = "" &
> request.Form("income_no_end_year") = "" then
> strincome_no_enddate = ""
> else
> strincome_no_enddate = request.Form("income_no_end_month") & "/" &
> request.Form("income_no_end_day") & "/" &
> request.Form("income_no_end_year")
> end if
> 
> I have the SQL Database field set to accept NULLS, it is a datetime
> field.

Assuming that you are then putting strincome_no_enddate into some SQL
statement, you are going to want to not set it to an empty string.
Additionally, I would use ORs instead of ANDs in the condition.
Otherwise, you'll get errors due to, say, a missing month, if either
the year or day is present. (You might want to do something else in
this case, but this is better than your current handling, IMHO.) So,
it'd look like so:

if request.Form("income_no_end_month") = "" or
                request.Form("income_no_end_day") = "" or
                request.Form("income_no_end_year") = "" then
        strincome_no_enddate = "NULL"
else
        strincome_no_enddate = request.Form("income_no_end_month") & "/" &
                request.Form("income_no_end_day") & "/" &
                request.Form("income_no_end_year")
end if

Also, make sure you don't surround strincome_no_enddate with single
quotes, because then it'll become the string 'NULL' when what you want
is the (non)value NULL.


Thanks,


-- 

Matt Warden
Berry Neuroscience Lab
Department of Psychology
Miami University



This email proudly and graciously contributes to entropy.


More information about the thelist mailing list