Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MSSQL Server - inserting blank in datetime field gives show up as 1/1/1900 12:00:00 AM

Posted on 2011-02-28
5
Medium Priority
?
559 Views
Last Modified: 2012-05-11
I need some help.  I've got a form that I created on my home system (SQL Server 2005) - works fine, but when I connected everything and ran some test inserts on my work box (also Server 2005) the dates, unless explicitly assigned a specific date, appear as 1/1/1900 12:00:00 AM.

The form has several optional fields where the registrant selects a skill (cpr, first aid, etc) and then adds a date from a calendar for the certification date.  Thus, not all dates are filled.  On the back side, I do assign anything blank a NULL but that doesn't work here for some reason - it just inserts the default date.

I can't say I've run into this before and what I've found on other sites hasn't worked - i.e. setting the default field value to null, etc.  If I set to NULL, the query works but inserts the 1900 date.  If I set it to 'NULL', I get an error about converting string to datetime and so on.

It's not a deal breaker - more of an irritant I'll have to deal with in reporting - so it would be nice to be able to get the fields without dates as null.

Can any one tell me what I'm doing wrong in this instance?

Many thanks in advance.
0
Comment
Question by:saabStory
[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
5 Comments
 
LVL 25

Assisted Solution

by:reb73
reb73 earned 500 total points
ID: 34999660
A value of 0 will get translated to the value indicated, there is probably either a default in the table or something in the application connection layer that forces null values to 0.

You could get away with having a trigger or better still handle it during reporting by using NULLIF(<field>, 0)
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 500 total points
ID: 34999691
NULL will work
declare @t datetime
set @t = null
SELECT @t
0
 
LVL 15

Assisted Solution

by:derekkromm
derekkromm earned 500 total points
ID: 34999844
Go into design mode and see if the column definition has a default value of 0? If so, remove that and make sure your column is nullable.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 35000000
as indicated above: 'NULL' is not NULL , '' is the same as 0 for dates (actually what you see),
so, you have to put some code somethere to consider the case.

either in the insert/update trigger on the table, to replace the "0" date by NULL, or in the actual PHP code to use NULL and not ''
0
 

Author Comment

by:saabStory
ID: 35030398
Sorry guys - got caught up in a product launch and just buried.  Try as I might, I could not get that field to take a null.  I tried everything here and it was set properly from my end - might be something somewhere else that I couldn't see.

Nevertheless, I was realized that the dates was really just text and would never be treated as a real date for purposes of comparison or anything.  With that caveat, I just changed the data type and finished up.  Not the best of solutions but it works for my purposes at the moment.  Now that the heat is off, I can spend more time exploring this with our dba.

Many thanks to all the suggestions and examples.
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

721 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