October 16, 2003 at 9:12 am
SQL Server Datetime datatype allows dates from year 1753-9999. I need to store data as old as 01/01/0001 due to package application requirements. Is there a way to get around this?
Edited by - afaa on 10/16/2003 09:13:16 AM
October 16, 2003 at 9:33 am
Use character fields
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
October 16, 2003 at 2:21 pm
You mean use Char or Varchar? But that will loose the benefit of datetime data type when it comes to date calculations, and comparisons and so forth, wouldn't it?
October 17, 2003 at 12:40 am
quote:
You mean use Char or Varchar? But that will loose the benefit of datetime data type when it comes to date calculations, and comparisons and so forth, wouldn't it?
Can you explain what you are trying to achieve?
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
October 17, 2003 at 4:25 am
Use the ISO format in the char field but you are sorta gonna be screwed on the datetime functions. Dates that far back are subject to errors as the calendar is not accurate due to changes over the years. Why 0001?
October 17, 2003 at 5:33 am
quote:
But that will loose the benefit of datetime data type when it comes to date calculations, and comparisons and so forth, wouldn't it?
Yes, but it doesn't matter because using dates prior to 1753 aren't acceptable by SQL Server as datetime anyways, so you can't use datetime calculations anyways.
-SQLBill
October 20, 2003 at 4:01 pm
I'm trying to see if we can migrate our database which is currently running on Informix in Unix. The application is Baan (ERP) package. The application populates blank (null) date fields with 01/01/0001. Thats the way the app deals with blank/null dates. It also uses date functions to calculate the date data.
Does anyone run Baan on SQL Server or knows someone that does?
October 21, 2003 at 5:24 am
Unless the date is stored in a character type field I was not aware Informix supported anything older than 12/31/1899. The fact they didn't use NULLS bugs me as well as somewhere they have to be validating a usable date. I would look at the informix datatype and see if it is not a character type as opposed to datetime.
October 21, 2003 at 8:03 am
Informix does support dates of 01/01/0001-12/31/9999. It stores dates as 4 byte integers equal to the value since 12/31/1899. We've been running it for many years now.
The reason Baan stores null date as 01/01/0001 is when it creates the tables it sets all columns to not null. Hence, every field requires some input. For dates it puts 01/01/0001, for strings it puts (space), for integers it puts 0(zero) instead of nulls.
October 22, 2003 at 6:27 am
Do not know anything about Baan (ERP) but can you convert 01/01/0001 dates to 01/01/1900 which would be sql server equivalent.
Far away is close at hand in the images of elsewhere.
Anon.
October 22, 2003 at 8:07 am
Since SQL 2K can store dates as early as '1/1/1753', when I need to reset a date to NULL (I wish :-)), I set it to '1/1/1754' (so that I know that _I_ put it in there). When my Stored Procedures and my applications see that date, they display and otherwise treat the field as if it were NULL or blank. (Note that the procedures and apps need to be programmed to respond that way). If you need to show historical dates prior to 1/1/1753, you need to use char or varchar.
Allan
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply