Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
How to store many datetime fields?

Answered How to store many datetime fields?

  • Monday, February 20, 2012 7:15 AM
     
     
    Sign In to Vote
    0
    I face the need to extend a database. I should store about 30 datetime fields somehow in a table. 30 columns is not a good idea I think. So what can you advice to store so much datetime fields? Maybe varchar with a specified delimeter?
     

All Replies

  • Monday, February 20, 2012 8:40 AM
    Moderator
     
     Proposed Answer
    Sign In to Vote
    0
    XML data type might be a choice. However, you may tell us why you need 30 datetime fields in the first place.

    Alex Feng | SQL Server DBA, ALIBABA.COM
    My Blog | MCTS: SQL Server 2008, Implementation and Maintenance
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

     
  • Monday, February 20, 2012 8:43 AM
     
     
    Sign In to Vote
    0

    I'm interested to know why you "must" store 30 datetime columns in a single relation. On the surface it sounds like you may want to revist the logical model of your database design.

    Perhaps you could share a little more infomration concerning your scenario?

    It's difficult to propose alternatives without first understanding what it is you are trying to model however an often typical alternative to what you are proposing would be to store a single datetime column and differentiate the values based on some sort type column, which itself would be a foreign key to a Type relation.

    John Sansom | SQL Server DBA Blog | @JohnSansom on Twitter



    • Edited by John Sansom Monday, February 20, 2012 8:43 AM
    • Edited by John Sansom Monday, February 20, 2012 8:45 AM
    •  
     
  • Monday, February 20, 2012 11:58 AM
     
     
    Sign In to Vote
    0
    A user can buy a ticket on 30 days (concrete days) chosen by himself.
     
  • Monday, February 20, 2012 12:19 PM
     
     Answered Has Code
    Sign In to Vote
    1
    A user can buy a ticket on 30 days (concrete days) chosen by himself.

      The I guess we can think for a table where we could have the details like follows

      

    CREATE TABLE TICKETDETAILS(USERID INT,TICKETDATE DATETIME)

    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed As Answer by Iric WenModerator Monday, February 27, 2012 8:36 AM
    • Marked As Answer by FofanovIS Wednesday, February 29, 2012 4:06 PM
    •  
     
  • Monday, February 20, 2012 3:44 PM
     
      Has Code
    Sign In to Vote
    0

    A user can choose 30 different dates and you advice me to

    CREATE TABLE TICKETDETAILS(USERID INT,TICKETDATE DATETIME)

    And where do you want to store 30 datetime fields?

     
  • Monday, February 20, 2012 4:30 PM
     
     Answered
    Sign In to Vote
    0

    As manish mentioned, you should consider storing all the dates in a different table and Join the table as necessary rather than denormalizing the dates. This will reduce your work of breaking the dates everytime you query for a selected date.

    If you need it in a single column for any specific reason, consider using XML.

    Planet Earth is at risk. Global warming is on a high tide.
    Take Responsibility. Plant Trees. Keep your City Clean and Green.

    Mark all Helping Posts and Close your Threads. Keep the Forum Green.
     - Arun Kumar Allu