locked
How to create a Date column to enter only date and month? RRS feed

  • Question

  • I am designing employee database where a column is: Salary_Increment_Date. But this column holds only day and month. Year is not needed as increment is to be applied on this day and month every year.

    Which data type is suitable for such a column?

     

    Wednesday, January 11, 2012 7:57 AM

Answers

  • Ok, but what year for April? 

     

    CREATE TABLE #t( c VARCHAR(4))

    INSERT INTO #t VALUES ('2404') --24 th of April

     

    Just keep thinking, you will be better of storing the data as datetime column (along with YEAR) for no messing up the year for searching and probably better performance 


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by OldEnthusiast Wednesday, January 11, 2012 9:43 AM
    Wednesday, January 11, 2012 8:13 AM

All replies

  • VARCHAR(4)....BTW, do you use that column in search? Means a WHERE condition?
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, January 11, 2012 8:00 AM
  • @Uri:

    I may have to use that column in search in future. I may need a report that fetches all employees whose increment is due in the month of, say, April.

    Wednesday, January 11, 2012 8:08 AM
  • Ok, but what year for April? 

     

    CREATE TABLE #t( c VARCHAR(4))

    INSERT INTO #t VALUES ('2404') --24 th of April

     

    Just keep thinking, you will be better of storing the data as datetime column (along with YEAR) for no messing up the year for searching and probably better performance 


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by OldEnthusiast Wednesday, January 11, 2012 9:43 AM
    Wednesday, January 11, 2012 8:13 AM
  • @Uri:

    As I wrote in OP, year is of no concern because increment is applied each year. What is of concern is the day and month?

    Wednesday, January 11, 2012 8:16 AM
  • Not at all. If you use it in WHERE condition then probably you need an index on that column
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, January 11, 2012 8:22 AM
  • A COLUMN is a specific unit of data. A date is a unit, and hence a data type. However, you want to record two pieces, the month and the day. Also, calling in "Date", is not technically correct, as it is an anniversary, not a date.

    Therefore, i would use two COLUMNs: Salary_Increment_Month and Salary_Increment_Day, of which both could be TINYINT, and appropriate CONSTRAINTs:

     

    Salary_Increment_Month TINYINT CHECK(Salary_Increment_Month <= 12)
    Salary_Increment_Day TINYINT	CHECK
    				(
    				 Salary_Increment_Day <=	CASE
    							 WHEN Salary_Increment_Month = 2 THEN 29
    							 WHEN Salary_Increment_Month IN (4, 6, 9, 11) THEN 30
    							 ELSE 31
    							END
    				)
    


     


    Wednesday, January 11, 2012 2:44 PM
    Answerer
  • I would most likely use the same design as Brian. It will make the queries use both fields, but yet from the design point of view this solution is better.

    BTW, how would you prevent invalid entries, such as 31 and 2?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Wednesday, January 11, 2012 7:38 PM
    Answerer
  • "It will make the queries use both fields"

    For speed or convenience, a VIEW, generated COLUMN, or even an INDEX could join them. But, let's get the data in appropriate fields first!

    "BTW, how would you prevent invalid entries, such as 31 and 2?"

    Was the CONSTRAINT incorrect?

    Wednesday, January 11, 2012 7:58 PM
    Answerer
  • Nope, sorry - didn't look into it too close. It is correct.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Wednesday, January 11, 2012 8:06 PM
    Answerer