Simplest way to store an array of DateTime values in SQL Server

Question

5.00/5 (1 vote)

See more:

What is the simplest way to store an array of DateTime values in SQL Server?
The length of the array can vary of course.

One option I thought of is using an xml column... Other suggestions?

Posted 21-Mar-15 22:12pm

Herbisaurus

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Abhinav S · Accepted Answer · 2015-03-21T22:17:00

Solution 1

Its an array. So use two tables.

In the first table mark the id of the datetime values.
In the second table, repeat the id and store the array items as rows one by one.

Table 1
ColA ColB DateId
..   ..   1
..   ..   2
...

Table 2
DateId DateValue
1      Date1
1      Date2
2      Date3
2      Date4
2      Date5
....

Posted 21-Mar-15 22:17pm

Abhinav S

Updated 21-Mar-15 22:21pm

v4

Comments

Tomas Takac 22-Mar-15 4:33am

I think you need to store the index of the element within the array too. Otherwise you won't be able to restore it to its original state. Unless of course the OP is happy with an unsorted array.

Abhinav S 22-Mar-15 4:49am

Fair point. Index is needed if order is important.

Herbisaurus 22-Mar-15 6:16am

धन्यवाद

phil.o · Accepted Answer · 2015-03-21T22:23:00

Multi-valued columns in a relational database is never a good idea.
The recommended way is to have a separate table with a datetime field and a reference field to the record in the original table it belongs to.

SQL

CREATE TABLE ORIGINAL (
   OriginalId int NOT NULL IDENTITY PRIMARY KEY
  ,--
)

CREATE TABLE YOURDATETIMES (
   YourDateTimeId int NOT NULL IDENTITY PRIMARY KEY
  ,OriginalId int NOT NULL REFERENCES Original.OriginalId
  ,Value datetime2 NOT NULL
)