none
Date format for Year, Year-Month, and Date (Year-Month-Day) where input is not known

    Question

  • I have a requirement to capture historical events in a table. Now some events will have only Year, some Year/Month, and remaining where Date is known.

    We should be able to store them in the column and be able to sort etc. When publishing the information, we should be able to publish as was the input, Year only, Year-Month only, or Date.

    I looked at the newer Date data type. I can insert a 4 digit year, but on retrieving it is YYYY-01-01. I do not know if it has input YYYY or YYYY-MM-DD and is indeed a Date or a Year. I am trying to avoid saving the format information in another column or something.

    For sorting, when records have same Year, I would have another column to do relative sorting...

    So question is - what are my best options with SQL/Entity Framework combo. And what others have done, when encountering similar - if any.

    Thanks in advance.

    --Sharad 

    Sunday, September 05, 2010 9:37 AM

Answers

  • I will recommend the approach of storing format information in another column and the date value in another column.

    if user specifies only Year(2000), store it as 2000-01-01, YYYY    

    if user specifies year(2000) and month(2),  store it as 2000-02-01,YYYY-MM

    if user specifies entire date, store it as 2000-05-12,YYYY-MM-DD

    If you didn't store the format information, if later user wants to edit records, user can't view the format he entered previously. so, for that purpose atleast, we need to store.

    see this example

    create table MyEvents
    (
    	Id int identity(1,1) primary key,
    	EventDate date,
    	DateType varchar(100)
    
    )
    
    declare @Year int
    set @Year = 2000 
    declare @Month int
    set @Month = 12
    declare @Day int
    set @Day = 0
    declare @Flag varchar(100) = 'YYYY'
    if(@Month <> 0)
    	set @Flag = 'YYYY-MM'
    if(@Day <> 0)
    	set @Flag = 'YYYY-MM-DD'
    
    insert into MyEvents 
     select DATEADD(DAY,isnull(nullif(@Day,0),1)-1,DATEADD(month,isnull(nullif(@Month,0),1)-1,dateadd(Year,@Year-1900,0))),@Flag
     
     
     select * from MyEvents order by EventDate,Id
    
    
    
    
    

    • Marked as answer by Sharad K Monday, September 06, 2010 12:47 PM
    Sunday, September 05, 2010 10:14 AM

All replies

  • I will recommend the approach of storing format information in another column and the date value in another column.

    if user specifies only Year(2000), store it as 2000-01-01, YYYY    

    if user specifies year(2000) and month(2),  store it as 2000-02-01,YYYY-MM

    if user specifies entire date, store it as 2000-05-12,YYYY-MM-DD

    If you didn't store the format information, if later user wants to edit records, user can't view the format he entered previously. so, for that purpose atleast, we need to store.

    see this example

    create table MyEvents
    (
    	Id int identity(1,1) primary key,
    	EventDate date,
    	DateType varchar(100)
    
    )
    
    declare @Year int
    set @Year = 2000 
    declare @Month int
    set @Month = 12
    declare @Day int
    set @Day = 0
    declare @Flag varchar(100) = 'YYYY'
    if(@Month <> 0)
    	set @Flag = 'YYYY-MM'
    if(@Day <> 0)
    	set @Flag = 'YYYY-MM-DD'
    
    insert into MyEvents 
     select DATEADD(DAY,isnull(nullif(@Day,0),1)-1,DATEADD(month,isnull(nullif(@Month,0),1)-1,dateadd(Year,@Year-1900,0))),@Flag
     
     
     select * from MyEvents order by EventDate,Id
    
    
    
    
    

    • Marked as answer by Sharad K Monday, September 06, 2010 12:47 PM
    Sunday, September 05, 2010 10:14 AM
  • Hmm if you do not know what format users are going to intsert into, then I would suggest VARCHAR(n) datatype... If you certainly know that fisrt four characters are YYYY year then to sort them just CAST to INT

    SELECT * FROM tbl ORDER BY LEFT(CAST(col AS INT),4) DESC


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, September 05, 2010 10:46 AM
  • As a variation of ramireddy's example, I like to propose this structure:

    CREATE TABLE somedata (
              keycol1    sometype NOT NULL,
              ...
              year       char(4) NOT NULL,
              month      char(2) NULL,
              day        char(2) NULL,
              datevalue  AS year + coalesce(month, space(2)) +
                                   coalesce(day, space(2)),
              CONSTRAINT pk_somedata PRIMARYT KEY (keycol1, ...)
              CONSTRAINT ck_month_day CHECK (
                  NOT (month IS NULL AND day IS NOT NULL)),
              CONSTRAINT ck_isdate  CHECK (
                  isdate(year + coalesce(month, space(2)) +
                                coalesce(day, space(2)) = 1)
    )

    There may be reason to persist and/or index the datevalue column.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Sunday, September 05, 2010 11:47 AM
  • I Wonder in ISDate()  function implementation, MS performed Ltrim() and rtrim()?.   :) results of these below statements are funny...

     

    select ISDATE('2000')
    select ISDATE('2000 ')
    select ISDATE('2000 ')
    select ISDATE(' 2000 ')
    select ISDATE('2000 01')
    select ISDATE(' 2000 01')
    

     

     

    Sunday, September 05, 2010 11:58 AM
  • Store Year, Month and date as separate column and as Integer to get fast retrival.

    CREATE TABLE tblYourTable
    (
    	ID int,
    	[YEAR] int,
    	[MONTH] int,
    	[DAY] int
    )
    
    

     

    And you can make decission if IsNull(Month,0) = 0 then ofcourse Day will be zero and if Year is zero then the rest of two will be definitely be zero. ANd Join them to form NONE, Year Only, Year + Month and Year + Month + Day.

     


    My Blog
    Monday, September 06, 2010 10:49 AM
  • I did some more analysis of current data, which will grow. Of about 150K records, ~26% are Year only and rest is indeed Date (dd MMMM yyyy). No YYYY-MM data as of now.

    So I am inclined to use Date column with Format info for those 26% records only. It seems simpler to accommodate in my EF model, sorting etc. I am trying to avoid having to do much work in SQL, and let Entity Framework handle the most for me. All I will need to do is, pick format from the DateFormat column when it is not null.

    Thanks everyone for quick turnaround.

    --Sharad

    Monday, September 06, 2010 12:47 PM