[thelist] ASP - SQL Date Time Help

Ken Schaefer ken.schaefer at gmail.com
Thu Aug 5 19:15:47 CDT 2004


Michael,

I have not read the article, however in SQL Server a NULL is *not*
1/1/1900. A 0 (as in zero) is 1/1/1900 when using smalldatetime. You
can check this by doing SELECT CAST(0 as SmallDateTime)
in Query Analyser.

To verify that a NULL is not 1/1/1900, just create a table, include
one field that is smalldatetime that allows nulls, create a new record
and do not enter any value for that field - you should see a NULL
value in there.

Cheers
Ken

On Wed, 04 Aug 2004 15:51:58 -0400, Michael Pack <michaelpack at wvdhhr.org> wrote:
> Yep, weird. I have tested my string with response.Write. For analytical
> purpose the start of the 1900's date will work for me.
> 
> Per that article I found it is the default value (even if NULL) of a
> SQL server datetime field, so you have to code around it.
> 
> >>> "Peter Brunone (EasyListBox.com)" <peter at easylistbox.com> 8/4/2004
> 3:35:26 PM >>>
> 
> 
> 
>   Hmm... that's really weird that it's putting in that date.  Have you
> done a Response.Write on your SQL string to make sure you're inserting
> what you think you're inserting?  Also, any chance there's a default
> value on that field?
> 
> Cheers,
> 
> Peter
> 
> Original Message:
> >From: "Michael Pack" <michaelpack at wvdhhr.org>
> 
> >Thank you all for your help.  AND thanks Matt for bringing up using
> OR
> >in the if statement. Since there is no client side validation on
> those
> >fields that could have caused problems.
> >
> >I finally got the insert to work but NULL is being converted to
> >"1/1/1900" .  That led me to finding this
> >http://www.c-sharpcorner.com/Code/2003/Sept/EnterNullValuesForDateTime.asp
> 
> >. Now I'm searching for the ASP/VB way to do it.
> >
> >Peter, I use & and AND and have no problems. I think AND is a bit
> more
> >legible.
> >
> >Many thanks, I'm getting closer!
> >
> >MP
> >
> >>>> "Peter Brunone (EasyListBox.com)" <peter at easylistbox.com>
> 8/4/2004
> >12:53:22 PM >>>
> >Hi Michael,
> >
> >   If you insert an empty string into a field, the field will contain
> >an empty string, not a null.
> >
> >   You need to set strincome_no_enddate to null (just the word
> "null")
> >so that the query looks like this:
> >
> >INSERT INTO tblWhatever(field1, field2)
> >VALUES('thisone', null)
> >
> >   Notice that there are no quotes around the null value.  If you put
> >quotes around it (like you would if it were a datetime string), the
> DB
> >will think you want to insert a string containing the word null,
> which
> >will of course result in another error.
> >
> >   Another approach is just not to insert anything for that field,
> but
> >that may be a bit more coding to change the structure of your query.
> >
> >   One last thing... are you using an ampersand (&) as an "And"
> >operator in your If...Then statement?  I didn't think that was
> >possible.
> >
> >Regards,
> >
> >Peter Brunone
> >_______________
> >EasyListBox.com
> >
> >Original Message:
> >>From: "Michael Pack" <michaelpack at wvdhhr.org>
> >
> >>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.
> >>
> >>I keep getting the following error when I run the transaction
> without
> >>selecting an end date in the form:
> >>
> >>Microsoft OLE DB Provider for SQL Server (0x80040E07)
> >>The conversion of a char data type to a datetime data type resulted
> >in
> >>an out-of-range datetime value.
> >>
> >>I've done a response.Write for the string and it is empty.
> >>
> >>Any assistance is greatly appreciated.
> >>
> >>MP
> >
> >
> >--
> >* * Please support the community that supports you.  * *
> >http://evolt.org/help_support_evolt/
> >
> >For unsubscribe and other options, including the Tip Harvester
> >and archives of thelist go to: http://lists.evolt.org
> >Workers of the Web, evolt !
> >--
> >* * Please support the community that supports you.  * *
> >http://evolt.org/help_support_evolt/
> >
> >For unsubscribe and other options, including the Tip Harvester
> >and archives of thelist go to: http://lists.evolt.org
> >Workers of the Web, evolt !
> >
> >
> 
> --
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
> 
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt !
> --
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
> 
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt !
>


More information about the thelist mailing list