how can I store date values partially?

Question

hello, every one

i'm using ms sql server 2005 and i need to store date values partially
for example, the day part of a date may stay unknown in fact but mssql constrains to specify the full date like the follow:

INSERT foo(dt) VALUES('2001-10-31');

though i would like to use something like this:

INSERT foo(dt) VALUES ('2001-10-??');

of course, ms sql doesn't allow to do such expressions, and i've found a rough example that converts date parts into multipliers:

SET NOCOUNT ON 
CREATE TABLE foo ( 
    dt INT 
) 
INSERT foo VALUES ( 
    DATEPART(YEAR, GETDATE()) * 10000 
        + DATEPART(MONTH, GETDATE()) * 100 
        + DATEPART(DAY, GETDATE()) 
) 
SELECT dt FROM foo 
DROP TABLE foo

... oooof, i can't believe if this is the only way to solve such problem, so i would like to ask: how can i solve this problem in the best way?

thank you for advices

Answer 1

Smart date keys are definitely the way to go. Your code is a little long, though, as SQL Server does provide a quick way to get it, using convert:

select cast(convert(varchar, GETDATE(), 112) as int)

You would think that you could convert this straight to an int, but it doesn't work. So you have to convert to a varchar and then an int.

If you want it to be 00 for the day, in lieu of the actual day:

select cast(convert(varchar, getdate(), 112) as int) - day(getdate())

Hopefully this helps!

Answer 2

Have you considered storing your month, day, and year in separate columns? It would be a very rudimentart & simplistic approach but sometimes that's the way to go. This would allow you to easily query values for a particular month and/or day without a lot of type conversions that may confuse the heck out of someone else that may need to work on the code down the road.

Answer 3

here you can store each date part, and still constrain them to a valid date:

CREATE Table PartialDates
(
     YearPart       smallint --(2 bytes -32k to +32k)
    ,MonthPart      tinyint  --(1 byte 0 to 255)
    ,DayPart        tinyint  --(1 byte 0 to 255)
    ,CompleteDate  AS (CONVERT(datetime,CONVERT(varchar(10),YearPart)+'-'+CONVERT(varchar(10),MonthPart)+'-'+CONVERT(varchar(10),coalesce(DayPart,1))))
)
ALTER TABLE dbo.PartialDates ADD CONSTRAINT
    CK_PartialDates_IsDate CHECK (ISDATE(CONVERT(varchar(10),YearPart)+'-'+CONVERT(varchar(10),MonthPart)+'-'+CONVERT(varchar(10),coalesce(DayPart,1)))=1)
GO

insert into PartialDates (yearpart,monthpart,daypart) values(2009,91,1) --error
insert into PartialDates (yearpart,monthpart,daypart) values(2009,1,1)  --ok
insert into PartialDates (yearpart,monthpart,daypart) values(2009,1,51) --error
insert into PartialDates (yearpart,monthpart,daypart) values(2009,2,29) --error
insert into PartialDates (yearpart,monthpart,daypart) values(2009,2,28) --ok
insert into PartialDates (yearpart,monthpart,daypart) values(2009,2,null)--ok

select * from PartialDates

YearPart MonthPart DayPart CompleteDate
-------- --------- ------- -----------------------
2009     1         1       2009-01-01 00:00:00.000
2009     2         28      2009-02-28 00:00:00.000
2009     2         NULL    2009-02-01 00:00:00.000

(3 row(s) affected

Answer 4

i don't know whether it's a good solution, but i can use clr user-defined types instead... i'll check it