Date insertion problem (01/01/1900)

Results 1 to 3 of 3

Thread: Date insertion problem (01/01/1900)

  1. #1
    Join Date
    Dec 1969

    Default Date insertion problem (01/01/1900)

    Hi,<BR><BR>I am having problem inserting datetime value into SQL7 database.<BR>Details as follows : <BR><BR>1. Databse is SQL7<BR>2. Server using British Datetime format<BR>3. using code ..sql = " INSERT INTO tb_123 VALUES(" & ...<BR>4. Checked the sql statement, it&#039;s fine (value : 28/02/2003)<BR>5. Field value is Datetime<BR>6. It seems ok if I use other method to insert (objRS.AddNew..objRS.update,etc)<BR><BR>What could be the problem? <BR><BR>thanks<BR><BR>James<BR>

  2. #2
    Join Date
    Dec 1969

    Default show us some code <eop>


  3. #3
    Join Date
    Dec 1969

    Default It is NULL

    This is one of the most misunderstood things about Date<BR>An empty string put into a Datetime field of a Database gives you a Date<BR>so <BR>Insert into MyTable SET MyDate = &#039;&#039;<BR>Will produce<BR>January 1, 1900<BR>Why is this?<BR>Because Datetime is NOT a text field AND it MUST have a valid date so it takes the lowest value in it&#039;s range<BR><BR>How can I enter a null date?<BR>Insert into MyTable SET MyDate = NULL<BR>Easy<BR>So when you are checking your text string for &#039; in the data don&#039;t forget to check your Date strings with<BR><BR>For Access<BR>&#060;%<BR>if len(trim(MyDateString)) = 0 then<BR> MyDateString = "NULL"<BR>else<BR> MyDateString = "#MyDateString#"<BR>end if<BR>%&#062;<BR>For SQL and most other Databases<BR>&#060;%<BR>if len(trim(MyDateString)) = 0 then<BR> MyDateString = "NULL"<BR>else<BR> MyDateString = "&#039;MyDateString&#039;"<BR>end if<BR>%&#062;<BR>Note I put the single quote round the MyDateString! <BR><BR>So when you do the wonderfull insert<BR><BR>Insert into MyTable SET MyDate =" & MyDateString<BR>you don&#039;t need to check if it&#039;s a Null or a Date!<BR><BR>Does that make sense!<BR>I hope so :-)<BR>Happy Dates......

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts