Convert 01-01-1900 Date string value to NULL

Hi

 

I have a number of Textboxs on a Formview.

 

The Textboxs contains Date values.

 

When I update these dates are saved to SQL Default 1/1/1900.

 

I know I have to

  • Convert Date string value to NULL before UPDATE query is executed
  • Or write a trigger to replace 1/1/1900 values to null  

I cannot find a way of converting Date back to NULL.

 

Also, I have only written very simple triggers.

 

Could anyone help me on to how to achieve the above?

 

Thanks in advance.

 

Using VB 2005/SQL 2005

0
yazzy
8/2/2006 11:02:12 AM
asp.net.presentation-controls 72751 articles. 3 followers. Follow

2 Replies
3930 Views

Similar Articles

[PageSpeed] 26

Have default datetime value in the table of null, rather than a minimum value.

If you use stored procedures for database update, then set the SP date parameter to default to null.  E.g. @SomeDateParameter datetime = null

When you call the stored procedure, dso not set the parameter if the date is missing, then in your insert/update query, a null value is inserted.

You can also use an Add/Update query using the same trick to pass the record Id.  If it is null then create a new record, otherwise update the row with the ID passed - saved writing two SP's

When you get the data from the database, then for null records, display empty text

 

 


If it was easy, everybody would be doing it.
0
sbyard
8/2/2006 7:32:24 PM

Can use 'ConvertEmptyStringToNull'

<

asp:Parameter Name="loggeddate" Type="String" ConvertEmptyStringToNull="true" />
0
yazzy
8/9/2006 10:14:11 AM
Reply:

Similar Artilces:

Null date textbox returns 01/01/1900 instead of null
VS 2005, SQL Server 2000 For the following textbox, if the textbox is blank when I do an update, it returns 01/01/00 (which is 01/01/1900).  Is there a way to prevent it from entering a value when it should be null, or do I need to do a check in the Updating event? <asp:TextBox ID="ResolvedDate" Text='<% #Bind("ResolvedDate","{0:MM/dd/yy}")%>'  runat="server" ></asp:TextBox>   I'm a little confused... Do you mean, when the page loads, the textbox has 01/01/1900... OR do you mean, when the page post...

Updating database date field results in date value of "01/01/1900"
Brand new to this, so please bear with me.I'm using the following code fragment to update a datetime field on a SQL Server 2005 database table:cmd.CommandText = "Update Projects Set EntryDate = " & Convert.ToDateTime(txtEntryDate.Text)cmd.ExecuteNonQuery()The result of the update operation is the the database field contains the value "1900-01-01 00:00:00:000".  This probably means that I passed nulls to SQL; however, I see a valid date in the txtEntryDate field on my web form (i.e., "06/18/2007").  I also did a "Response.write" t...

date is 01/01/1900
I'm updating a row in sql2k and its updating my date field to 01/01/1900 no matter what I specify. Am I doing this correctly: UPDATE Purchase SET DOP = 25/03/2003 WHERE Code = 'code02' I'm using query analyzer also, same thing, although row updated successfully is 1. > UPDATE Purchase SET DOP = 25/03/2003 WHERE Code = 'code02' try : UPDATE Purchase SET DOP = '25/03/2003' WHERE Code = 'code02' or in access UPDATE Purchase SET DOP = #25/03/2003# WHERE Code = 'code02' though I usually try to use non-ambiguos date formats e.g. UPDATE Purchase SET DOP = '25 Mar...

date is 01/01/1900
I am using PB12.1 and MS SQL Server 2008 R2 Express , and I am connect through SNC I have code like::- ls_date = "28/12/2010" format is dd/mm/yyyy date ldt_date ldt_date = date(ls_date) but it gives date=01/01/1900 it means null date and in control panel\reginal setting I change date format to dd/mm/yyyy but problem still, could any one help me thanks That says the input was not recognized as a valid format - null input would return null. It's a better practice to use PB's date literal format "yyyy-mm-dd" to avoid problems, as the control panel se...

date is 01/01/1900
I am using PB12.1 and MS SQL Server 2008 R2 Express , and I am connect through SNC I have code like::- ls_date = "28/12/2010" format is dd/mm/yyyy date ldt_date ldt_date = date(ls_date) but it gives date=01/01/1900 it means null date and in control panel\reginal setting I change date format to dd/mm/yyyy but problem still, could any one help me thanks I just answered this question in the general section. Please don't crosspost - answers are more useful if the discussion is all in one place. Report Bugs to Sybase: http://case-express.sybase.com/cx/welcome.do ...

Inserting a NULL value instead of 01/01/1900 12:00:00 a.m.
Hello, I'm trying to save data from my asp.net page but even when I'm adding the NULL value in my query is still saving the 01/01/1900 12:00:00 a.m. in my datetime field This is my code:   Dim sqlConn As SqlConnection Dim sqlCmd As SqlCommand Dim strConnection, resul, COP, USD As String strConnection = ConfigurationManager.ConnectionStrings("SIPConnectionString").ConnectionString sqlConn = New SqlConnection(strConnection) COP = Replace(TxtCOP.Text, ".", ",") USD = Replace(TxtUSD.Text, ".", ",") resul = "Insert into...

NULL DATE IS NOT NULL BUT 01/01/00 00:00
The following works if I'm in design mode, but it doesn't in the release version. I got a column field with a mask as mm/dd/yy hh:mm, when the value is null, it shows 00/00/00 00:00 which is what I want, but when user wants to delete an existing date, let's say 06/05/02 12:00, instead of going back to 00/00/00 00:00 it goes to 01/01/00 00:00. I am using PB 8 Ent and W2K operating system, database is in SQL2000. If I look at the value in the database it got 01/01/00 instad of <NULL>. try check 'blank is null' @ datawindow field setting... & allow ed...

Default Date 01/01/1900 ro current date
Is there a way to change the PB default date of 01/10/1900? Is it a built-in setup or is there a setting I can change to make it use the current date instead of 10/10/1900? Thanks for your help! No. I believe PB uses an "epoch" integer for internal date storage, and 01-jan-1900 is what 0 translates to. You're just going to have to initialize ate variables as needed. <Anna> wrote in message news:4238932b.391a.1681692777@sybase.com... > Is there a way to change the PB default date of 01/10/1900? > Is it a built-in setup or is there a setting I can change t...

1900/01/01 date issue
Hi, I have two date fields(date1 and date2) on a dw, both with editmasks in yyyy/mm/dd format. When the user enters 0000/00/00 in the first date field (date1), debugger shows date1's value as 1900/01/01 - the default value for invalid dates as I understand. The application has processing in the itemchanged event of this dw to SetNull date2 if date1 = 1900/01/01 and if not, do some processing and display a new date in date2. Is there a standard work around to allow the user to enter 1900/01/01 in date1, check for invalid dates, and still allow normal processing (perform the ca...

Hide 1900-01-01 date in GridView
I use a Calendar in a GridView. I found the Calendar does not like the NULL from the database so I replaced NULL by SqlDateTime.Null which is actually the date 1900-01-01. So now I see 1900-01-01 in the GridView. How could I replace 1900-01-01 by nothing in the GridView ? Hi Guy,  I have done something similar with the following code using the pre render event for the grid view:   Protected Sub GridView1_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.PreRender For Each r As GridViewRow In GridView1.Rows If r.Cells(<da...

Date function return 1900/01/01
I have a datetime column. I want to get it value and compare to another datetime column. Here is my script in itemchanged datetime ldt_source, ldt_target string ls_date ls_date = gettext() ====> return 2002/10/16 00:00:00 ldt_source = date(ls_date) ====> return 1900/01/01 How do I convert a string to date, ref to help. It is using date(). But it return 1900/01/01 It's strange that you don't get a compile error. ldt_source is datetime In that case you should do something like: ldt_source = Datetime(Date(ls_date)) What you can always do is strip of the t...

Function for converting a specific date to number of seconds since 01/01/1970?
Hi, I need to know the easiest way to translate a date (not today's date) into the number of seconds since 01/01/1970. I realize there is at least one function that turns the current date/time into this number, but I need to find, for instance, everything with a date/time stamp between 01/01/1998 and 01/01/2001. How is this done? Thanks, Alan ===== _____________________________________________________ "Hello. I'm Marc Bolan. I'm a superstar. You'd better believe it" _____________________________________________________ ___________________________...

EditMask Style on DateTime (Oracle) Field Yields 01/01/1900 Date
I am running PB7 over an Oracle 7.3 database. I have set the EditMask style on my datetime fields to 'MM/DD/YYYY', have set a Format (Display) of 'MM/DD/YYYY,' and have set the 'Use Format' property on the Edit property tab. Everything works beautifully until I erase an entered value from the field by highlighting and pressing the Delete key. When I tab off the field, the date '01/01/1900' populates the field. From the on-line help, I have found that 01/01/1900 displays when there is an error in a date function--when an incorrect format is used. Wh...

Strange empty mail dated 1970-01-01 01:00
Hello In my Thunderbird 2.0.0.23 sometimes appears empty mails dated 1970-01-01. I can only delete them by rebuilding folder index. I created new profile but problem stayed. Could someone help. Best regards Slawek Poland __________ Informacja programu ESET NOD32 Antivirus, wersja bazy sygnatur wirusow 4465 (20090928) __________ Wiadomosc zostala sprawdzona przez program ESET NOD32 Antivirus. http://www.eset.pl lub http://www.eset.com Slawek wrote: > Hello > In my Thunderbird 2.0.0.23 sometimes appears empty mails dated 1970-01-01. I > can only del...

Web resources about - Convert 01-01-1900 Date string value to NULL - asp.net.presentation-controls

Roger's 'Gabriola' Mansion (1900-01) - 1523-1541 Davie St. - Flickr - Photo Sharing!
Heritage Register: A-listed Heritage Designated (Legally protected) Built 1900-01 Architect: Samuel Maclure Original owner: B. T. Rogers The ...

Resources last updated: 2/3/2016 1:19:08 AM