[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

MS SQL DEFAULT DATE 1/1/1900

Posted on 2001-07-22
7
Medium Priority
?
512 Views
Last Modified: 2008-03-03


I want to prevent the default value of 1/1/1900 from beign inserted into the date field in the table

How do I change this value to NULL?
I tried this but didn't work

Any suggestion will help
====================================


sTHE_DATE=Request.Form("THE_DATE")
If sTHE_DATE="" Then
sTHE_DATE="NULL"
End If

sql to update.....................

0
Comment
Question by:iyiola
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 33

Expert Comment

by:hongjun
ID: 6306848
Make sure that the field is not set to be the default date when this table is created. Also make sure that this field allows NULL values.

hongjun
0
 

Author Comment

by:iyiola
ID: 6306914
hongiun,
The field allows default values.
The problem is that SQL server is not taking "NULL" cos  the datatype is datetime



0
 
LVL 2

Expert Comment

by:xinger
ID: 6306924
I think a CASE expression embedded into your UPDATE SQL is a possibility. I'm not sure of the exact syntax off the top of my head, but it is something like the following.

sTHE_DATE = sTHE_DATE=Request.Form("THE_DATE")

sUPDATE_SQL = "UPDATE TheTable SET TheDateField = CASE " & sDATE & "WHEN '' THEN NULL ELSE " & sDATE & " END ..."
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 33

Accepted Solution

by:
hongjun earned 80 total points
ID: 6307026
Ok. Does the field allow NULL field? If yes you can insert a NULL field for that. Note that NULL is allowed but the string "NULL" is not allowed.


Try this

<%
sTHE_DATE = Request.Form("THE_DATE")
If sTHE_DATE = "" Then
    strSQL = "update your_table set your_date = null"
else
    strSQL = "update your_table set your_date = '" & sTHE_DATE & "'"
End If

objConn.Execute(strSQL)
%>


hongjun
0
 
LVL 1

Expert Comment

by:loveneesh_bansal
ID: 6307227
There is no way you cannot stop the sql server to take the 1/1/1900 value but you can solve you problem by making the field varchar and later on in the programming part convert this value into the date by using the date functions. Because when i have the similar problem then i solve this by this way only.

bye

 
0
 
LVL 2

Expert Comment

by:Lunchy
ID: 6375022
Hi iyiola

In the interest of maintaining the database of questions, it would be good if you could resolve your question.  Your options at this point are:

1. Award points to the Expert who provided an answer, or who helped you most. Do this by clicking on the "Accept Comment as Answer" button that lies above and to the right of the appropriate expert's name.

2. Award points to multiple experts--If you wish to award multiple participants, you can do so by creating a zero point question in the Community Support topic area, include this link and tell them which experts you'd like to award what amounts.

3. PAQ the question because the information might be useful to others, but was not useful to you. To use this option, you must state why the question is no longer useful to you, and the experts need to let me know if they feel that you're being unfair.

4. Delete the question because it is of no value to you or to anyone else. To use this option, you must state why the question is no longer useful to you, and the experts need to let me know if they feel that you're being unfair.

If you elect for option 2, 3 or 4, all you need to do is post right here as a comment, and I will take care of the rest.

PLEASE DO NOT AWARD THE POINTS TO ME. We also request that you review any other open questions you might have and deal with them as necessary.

Lunchy
Friendly Neighbourhood Community Support Moderator
Community Support:
http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
0
 
LVL 2

Expert Comment

by:Lunchy
ID: 6395101
Force accepting hongjun's answer.
It is pretty clear you can't assign a string to a date field.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question