SELECT, manipulate data temporary using SQL Server

Question

The DB has a field "end date" and 1753/1/1 is supposed to be the value representing nothing, as we cannot assign null to datetime in SQL Server..

In reporting application the select query used, gets the data including those 1753 date values. good

Problem

1753 date causes hell with report calculations. There are a lot of calculations in the report and changing the code to ignore 1753 will be a headache, so I was thinking

Is there a way to tell SQL Server:

SELECT * 
FROM TABLE BUT IN END_DATE 
WHERE VALUE='1753/1/1 01:00:00.000' REPLACE WITH PARAMETER1

CRUDE EDIT (do not have access to debugger currently)

I cannot remember but I get an error like

nothing should be mapped on something

when I try to insert record with null date! THIS HAPPENS ONLY WHEN USING VB.NET in C# it works fine. Do you know why??

Why can't you assign null to a datetime in SQL Server, by the way? — Matt Gibson
– Matt Gibson, Commented Aug 9, 2011 at 8:35
i cannot remember but i get and error something saying nothing should be mapped on something when i insert record with null date! THIS HAPPENS ONLY WHEN USING VB.NET in csharp it works fine. do you know why?? — Janth
– Janth, Commented Aug 9, 2011 at 8:45
Post your code which raises error when saving null, perhaps it could be because DateTIme is value type so you can't assign NULL, so you have to save SqlDateTime() objects into the dateTime columns — sll
– sll, Commented Aug 9, 2011 at 8:48
thats exactly the case, i shall simulate the problem and post asap — Janth
– Janth, Commented Aug 9, 2011 at 8:54

sll · Accepted Answer · 2011-08-09 08:56:26Z

4

SELECT * 
FROM TABLE
WHERE END_DATE = 
 CASE VALUE 
   WHEN VALUE = '1753/1/1 01:00:00.000' THEN PARAMETER1 
   ELSE VALUE
 END

EDIT: Answer to comments

You can not assign NULL to DateTime fields because DateTime is a Value Type so it can't persist NULL. Use SqlDateTime.NULL to pass NULL value to stored procedure or query itself. If you can provide code which should pass a NULL - I can give you more detailed example.

edited Aug 9, 2011 at 8:56

answered Aug 9, 2011 at 8:34

sll

62.7k22 gold badges109 silver badges157 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bernhard Kircher · Accepted Answer · 2011-08-09 08:36:11Z

1

Although I think that null should be used to indicate that something does not have a value, you can try:

SELECT MyDate = CASE END_DATE   
    WHEN '1753/1/1 01:00:00.000' THEN YourDefaultValue
    ELSE END_DATE
END

See msdn for details.

answered Aug 9, 2011 at 8:36

Bernhard Kircher

4,2023 gold badges35 silver badges38 bronze badges

Comments

Alex_L · Accepted Answer · 2011-08-09 08:36:59Z

0

    SELECT field1, field2, field3, 
        'NEW_END_DATE' = CASE CAST(END_DATE AS DATE) = '1753-1-1' 
             THEN @Parameter 
             ELSE END_DATE 
         END
    FROM TABLE;

answered Aug 9, 2011 at 8:36

Alex_L

2,6641 gold badge15 silver badges13 bronze badges

Comments

Carlos Quintanilla · Accepted Answer · 2011-08-09 08:37:03Z

0

You need to use a case

SELECT case when date_field = '1753/1/1 01:00:00.000' then @PARAMETER1
ELSE date_field END
FROM TABLE

answered Aug 9, 2011 at 8:37

Carlos Quintanilla

13.4k3 gold badges24 silver badges25 bronze badges

Comments

Matt Gibson · Accepted Answer · 2011-08-09 08:43:59Z

0

I guess you're probably looking for something like this (note you can't do it with a SELECT *; you'll have to explicitly select the columns you need, unless you want to add the calculated column using a different name.)

SELECT 
   CASE 
      WHEN END_DATE = > '1753/1/1 01:00:00.000' THEN END_DATE 
      ELSE PARAMETER1 
   END 'END_DATE',
   other_columns...
FROM
   TABLE

But really, I'd recommend changing your design to use NULLs instead of this weird end date you're using. There's nothing special about DATETIME columns that stops them being NULLable. I worked on a project once where someone had done a similar thing with a dopey "invalid" date value, and it caused no end of problems.

edited Aug 9, 2011 at 8:43

answered Aug 9, 2011 at 8:37

Matt Gibson

38.3k10 gold badges103 silver badges130 bronze badges

2 Comments

Janth Over a year ago

this happens only using vb.net really! have any idea why.

Matt Gibson Over a year ago

Well, it sounds like @sll has a handle on that problem, so I'd follow that advice and switch to using NULL. It'll probably make your life easier.

Collectives™ on Stack Overflow

SELECT, manipulate data temporary using SQL Server

5 Answers 5

Comments

Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related