August 13, 2004 at 5:52 am
Hi,
It is very frustrating that SQL Server's largest date type can only go back to 1753. I'm building an application that will help manage historic newspapers and I'm in a bit of a dilemna of what to do about this date limitation. I believe my only option is to use an underlying CHAR(8) type formatted like '20040813'. Is this the best option? That will allow sorting and my .NET app will need to parse it for displaying. However, what I also need is all the standard date functions like DATEDIFF and DATEADD. I know it would be possible to write UDFs in Transact-SQL to perform these functions on my own underlying date type, but I'm looking for some guidance. I've scoured Google for information about this SQL Server limitation and to my surprise not much turned up. There has to be at least someone who has run into this problem and has come up with a workable solution. Are threre any custom UDFs out there to work with the CHAR(8) date representation? I'd rather not have to write them from scratch if this is my only option.
Thanks for any help,
Ben Hughes
August 13, 2004 at 6:18 am
Ben,
I would research the possibility of using an INTEGER field instead of CHAR(8). This way you could do simple math instead of
DATEADD(DAY, 90, NewspaperDate) == NewspaperDate + 90
I think this should work as long as you use YYYYMMDD. If you wanted to use Years you could : NewspaperDate + (NumYears + 365)
Good Hunting!
AJ Ahrens
webmaster@kritter.net
August 13, 2004 at 8:14 am
An integer field wouldn't do much good because arithmatic on YYYYMMDD as an integer doesn't take into account overflows on days (31 or 30) or months (12). Day arithmatic is built into SQL Server with the DATETIME or SMALLDATETIME type using the + operator. Unfortunately I'll have to write my own UDFs to do any modification on either a numeric or character type. A numeric type would allow for faster sorting and equality retrieval, but would require more overhead with my own date functions because of the conversion to a string to extract year, month, and day.
If I don't find a solution to this, I'm going to start writing replicate UDFs for DATEADD and DATEDIFF I think - Hopefully it won't be too complicated, but I'm not very experienced with Transact-SQL.
August 13, 2004 at 5:09 pm
My suggestion is to create a separate table to hold the sequence of dates you are interested in.
All tables that contain calender information will then reference this table on the pk.
For time information ( hours, minutes etc ) regular datetime columns can be used for that purpose.
Here is a sample script to create such a "dates" table.
This will get You started I guess, good luck.
set nocount on
-- drop table dates
-- create dates table
create table dates(
id int not null,
[date] as (yyyy+mm+dd),
yyyy char(4) not null,
mm char(2) not null,
dd char(2) not null,
[year] int not null check(year between 1582 and 4000), -- Gregorian calender from 1582 (in most countries)
[month] tinyint not null check(month between 1 and 12),
[day] tinyint check(day between 1 and 31),
day_of_week tinyint,
day_name char(18),
month_name char(18),
constraint pk_dates_id primary key clustered(id),
constraint uc_unique_date unique([date]),
constraint chk_valid_date check(isdate((yyyy+mm+dd)) = 1) )
go
-- Declare variables
declare @startdate datetime, @enddate datetime, @id int, @year int, @month int, @day int
-- Initialize variables
set @startdate = '20000101'
set @enddate = '20051231'
select @id = 1,@year = year(@startdate),@month = month(@startdate),@day = day(@startdate)
-- Populate dates table
while 1 = 1
begin
-- insert the date table
insert dates(id,yyyy,mm,dd,[year],[month],[day])
values( @id,
substring('0000',1,4-len(@year))+ltrim(@year),
substring('00',1,2-len(@month))+ltrim(@month),
substring('00',1,2-len(@day))+ltrim(@day),
@year,
@month,
@day )
-- check for end condition and return
if (select [date] from dates where id = @id) = @enddate return
-- recompute the variables
if @day < 28 set @day = @day + 1
else if @month in(1,3,5,7,8,10) and @day = 31 select @day = 1, @month = @month + 1
else if @month = 12 and @day = 31
begin
select @day = 1, @month = 1, @year = @year + 1
print @year
end
else if @month in(4,6,9,11) and @day = 30
select @day = 1, @month = @month + 1
-- A leap year is any year divisible by four except years both divisible by 100 and not divisible by 400
else if @month = 2 and @day = 28 and not (@year%4 = 0 and not( @year%4 = 0 and @year%100 = 0 and @year%400 != 0 ))
select @day = 1, @month = @month + 1
--else
else if @month = 2 and @day = 29 and (@year%4 = 0 and not( @year%4 = 0 and @year%100 = 0 and @year%400 != 0 ))
select @day = 1, @month = @month + 1
else set @day = @day + 1
set @id = @id + 1
end
go
-- Update the fields with name of month and weekday
update dates set
month_name = datename(mm,convert(datetime,date)),
day_of_week = weekdays.wd,
day_name = weekdays.dayname
from
( select id%7 as mod,datepart(dw,convert(datetime,date)) as wd,datename(dw,convert(datetime,date)) as dayname
from dates where date between '20040105' and '20040111' ) as weekdays
where
weekdays.mod = dates.id%7
go
select * from dates
--/rockmoose
You must unlearn what You have learnt
August 13, 2004 at 5:13 pm
Ok, forgot to add..
For performing datediff comparisons and such ( day level ) you just have to substract the id between 2 dates in the sample table.
You could add constraints on the table to make 100% sure that the id sequence is continuous and ascending.
Note, a table of this kind is just supposed to be created once and then only used for referencing.
/rockmoose
You must unlearn what You have learnt
August 13, 2004 at 6:56 pm
The code you gave uses several standard date functions that simply will not work with dates before 1753, which is the problem to begin with. The CHECK constraint you have uses ISDATE(). If you execute SELECT ISDATE('17520101'), it will return false. In that case, dates 1753 or above are only supported and I might as well just use a regular DATETIME column.
I think I am just going to have to use an underlying CHAR(8) or UNSIGNED INT field and rewrite all the standard date manipulation functions to use those. It baffles me how it seems no one else has ever run into this as being an issue. The one thread I picked up on Google did have someone with the same problem and he was just told to use Oracle instead, which supports dates in the far BC's. Of all the features and robustness SQL Server sports, you think they'd overcome this annoying simple issue. I do hear that date support in Yukon will be expanded, but I just can't wait that long.
Anybody have any other suggestions..?
August 14, 2004 at 3:22 am
Yes you are right, the code uses a couple of unusable standard date functions.
Just came home from vacation and the brain maybe is still on vacation 🙂
Here is an interesting link about dates for you:
http://scienceworld.wolfram.com/astronomy/GregorianCalendar.html
( might explain the 1753 sql server restricition )
Also The code can be easily modified:
1. get rid of the "chk_valid_date" contraint ( or rewrite )
2. update the month names from month column. 1 = 'January' etc...
I wanted to give You an alternative approach which IMO is quite efficient, and that I probably would use if I had your requirements.
I use date tables sometimes when I need a "rich" calender table.
For example the entries in the date table can be flagged with holidays etc.
Now go and skin the cat...
/rockmoose
You must unlearn what You have learnt
August 16, 2004 at 3:08 am
Hi, I have written an application which records details from books of accounts dating back to 1439. The project uses an MSDE (2000) database to store all the information about the double entry postings, accounts, participants and currencies. The Financial transactions contain a posting date (and some transaction store a 'settlement date').
As the range of transactions were recorded for a period between 1439 and 1444, I used a simple method of retaining a 'base century' (e.g. 1400) and a 'db century' (e.g.1900) in the preferences table of the database.
The transactions date field are MS SQL date fields, so 1st Feb 1439 is held as 1939-02-01 00:00:00 (UDF).
All the date controls on forms and reports use a short date regional setting (e.g. dd/mm/yy NOT the windows default dd/mm/yyyy). This can be checked by the application. When I need to see the 'full date' (e.g. 1st Feb 1439) i construct the string (e.g. for a report or text label).
I reported my frustration with this limitation, which is NOT a limitation in MS Access, to Microsoft (about 2 years ago), suggested another datetime type could be added (with a granualatity which favours history over precision), e.g. bigdatetime.
The application has been in a production environment for over two years. I decided not to re-write SQL server date functions but to use what was available and taylor and inputs and outputs.
I hope this is of some help.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply