April 20, 2011 at 3:03 pm
Is there a way to replace NULL to a datetime column instead of 1900-01-01 00:00:00.000 for NULL dates ?
April 20, 2011 at 3:24 pm
LOOKUP_BI-756009 (4/20/2011)
Is there a way to replace NULL to a datetime column instead of 1900-01-01 00:00:00.000 for NULL dates ?
I'm not quite sure I get the question. Can you rephrase what you're looking to do?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 20, 2011 at 3:29 pm
I have a column of type datetime.
Many of its value are NULL's but in the column it appears as 1900-01-01 00:00:00.
I know that 1900-01-01 00:00:00 is the default for datetime data type, But I would like to see NULL instead of 1900-01-01 00:00:00
Is this possible ?
April 20, 2011 at 3:41 pm
There is no default for the datetime datatype.
Probably what you are doing is inserting a zero-length string into a datetime column and that will get cast to a date of 1900-01-01 00:00:00.000. Insert an actual NULL into the column, and it will be NULL.
Here is an example of what is happening:
select ZeroLengthStringDate = convert(datetime,'')
Results:
ZeroLengthStringDate
-----------------------
1900-01-01 00:00:00.000
April 20, 2011 at 3:44 pm
LOOKUP_BI-756009 (4/20/2011)
I have a column of type datetime.Many of its value are NULL's but in the column it appears as 1900-01-01 00:00:00.
I know that 1900-01-01 00:00:00 is the default for datetime data type, But I would like to see NULL instead of 1900-01-01 00:00:00
Is this possible ?
As Michael above pointed out, there's some other conversion occurring here.
This code in 2k5:
use test
go
CREATE TABLE dtTest (myTestDT DATETIME NULL, otherdata VARCHAR(50) NULL)
INSERT INTO dtTest VALUES (NULL, 'abc')
INSERT INTO dtTest VALUES (0, 'bbb')
INSERT INTO dtTest VALUES ('20101231', 'ccc')
select * from dttest
Gives me this result:
NULLabc
1900-01-01 00:00:00.000bbb
2010-12-31 00:00:00.000ccc
Well, at least now I understand why I didn't understand. 🙂 What else is occuring with your code?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 20, 2011 at 3:50 pm
Craig & Michael, yup inserting a NULL in datetime clmn returns a NULL and empty string returned 1900-01-01 00:00:00.000.
So what I need is to replace all 1900-01-01 00:00:00.000 with NULL, as this data is coming from front
end app and while reporting the figures.We see lots of 1900-01-01 00:00:00.000. I would like to replace
1900-01-01 00:00:00.000 as NULL instead.
Can this be done ?
April 20, 2011 at 4:06 pm
LOOKUP_BI-756009 (4/20/2011)
Craig & Michael, yup inserting a NULL in datetime clmn returns a NULL and empty string returned 1900-01-01 00:00:00.000.So what I need is to replace all 1900-01-01 00:00:00.000 with NULL, as this data is coming from front
end app and while reporting the figures.We see lots of 1900-01-01 00:00:00.000. I would like to replace
1900-01-01 00:00:00.000 as NULL instead.
Can this be done ?
Well, you need to get the front end app changed long term, especially if it's sending down direct SQL and not going through a proc. If it's going through a proc, NULLIF should work find for you against the parameter.
For the historically entered data, you'll just need a one off mass update statement to run through. Something like:
UPDATE table SET dtField = NULL WHERE dtField = 0
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 21, 2011 at 12:14 pm
Front end changes are difficult, I ve added NULLIF to my proc .Thanks Craig
April 22, 2011 at 6:49 am
UPDATE table SET dtField = NULL WHERE dtField = 0
or maybe
UPDATE table SET dtField = NULL WHERE dtField = 0 Or dtField = '' ?
April 22, 2011 at 7:22 am
Stephen_W_Dodd (4/22/2011)
UPDATE table SET dtField = NULL WHERE dtField = 0
or maybe
UPDATE table SET dtField = NULL WHERE dtField = 0 Or dtField = '' ?
Remember dtField represents a field with a datetime data type and that the datetime takes precedence over both int and char data types. So, what does 0 evaluate to when converted to datetime? What does '' evaluate to when converted to a datetime? How does dtField = 0 differ from dtField = ''?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 22, 2011 at 7:32 am
Let's take that one step further. You can't actually have an empty string in a date, time, or datetime data type. If you try to store and empty string or a blank in any of those, it'll default to "0" behind the scenes which, of course, is 1900-01-01 00:00:00.000.
If someone made the mistake of storing dates as strings, then all bets are off. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply