CODEWITHSUNDEEP
c#sql-servermodel-view-controller
ExceptionLimeCat
ExceptionLimeCat

Reputation: 6400

Updating values in SQL Server database with MVC

We have a really stupid data requirement for dates in our database. The month, day, year, minute, & hour are stored in separate columns. Obviously, this is not ideal for querying date ranges especially using Entity Framework.

We decided to just create a new column called combinedDate that is a SQL smalldatetime with the correlating date info. While trying to update our db, our application just freezes and never completes the update. Our test db only has 8400 rows so this definitely shouldn't be a big problem.

Should we be using a stored procedure for this? What is the best approach to accomplish this?

We will have to do this to our date data for every new entry in the database. These entries could possibly happen 8400 rows at a time.

Column formats:

New column: smalldatetime

Upvotes: 0

Views: 446

Answers (4)

user756519
user756519

Reputation:

As others mentioned, creating a computed column would be a better choice. Completely avoiding this approach would be even better. However, if cannot avoid this approach, here is the computed column expression that you could use. In the output section you can notice, that invalid values in month and min actually add up to display the correct date in future

Computed column expression:

(dateadd(year, (100) + [year],
    dateadd(month, [month] - (1),
        dateadd(day, [day] - (1),
            dateadd(hour, [hour],
                dateadd(minute, [min], (0))
                )
            )
        )
    )
)

Sample script:

CREATE TABLE dbo.myTable
(
        [year]  int NOT NULL
    ,   [month] int NOT NULL
    ,   [day]   int NOT NULL
    ,   [hour]  int NOT NULL
    ,   [min]   int NOT NULL
    ,   [smalldatetime]  AS (dateadd(year,(100)+[year],dateadd(month,[month]-(1),dateadd(day,[day]-(1),dateadd(hour,[hour],dateadd(minute,[min],(0))))))) PERSISTED
);

INSERT INTO dbo.myTable ([year], [month], [day], [hour], [min]) VALUES
    (12, 4, 27, 17, 34),
    (12, 6, 12, 4, 8),
    (11, 32, 12, 54, 87);

SELECT  [year]
    ,   [month]
    ,   [day]
    ,   [hour] 
    ,   [min] 
    ,   [smalldatetime] 
FROM    dbo.myTable;

Sample output:

year month day hour min smalldatetime
---- ----- --- ---- --- -----------------------
12    4     27  17  34  2012-04-27 17:34:00.000
12    6    -12   4   8  2012-05-19 04:08:00.000
11    32    12  54  87  2013-08-14 07:27:00.000

Upvotes: 1

Shelakel
Shelakel

Reputation: 1070

I agree that a computed column is the best approach.

Perhaps you should consider a different angle; instead of computing the datetime, rather compute the year/month/day/hour/minute via

Year as (DATEPART('yyyy', Time)) persisted not null,
Month as (DATEPART('m', Time)) persisted not null,
Day as (DATEPART('d', Time)) persisted not null,
Hour as (DATEPART('hh', Time)) persisted not null,
Min as (DATEPART('mi', Time)) persisted not null,
Time datetime not null

Link to DATEPART documentation

That way you will automatically have the other fields validated when updating the Time column.

Disclaimer: At the time of this writing I'm unsure if DATEPART is deterministic to use persisted.

Upvotes: 1

thekaveman
thekaveman

Reputation: 4399

Rather than trying to do the update from the C# side, why don't you just specify a computed column for combinedDate. Take a look at the DATETIMEFROMPARTS built-in function, it may be of use

EDIT

As was pointed out in the comments, DATETIMEFROMPARTS is available only in SQL Server 2012, here is an alternative way to get a DATETIME from parts y, m, d (assuming integers):

CAST(CAST(y AS varchar) + '-' + CAST(m AS varchar) + '-' + CAST(d AS varchar) AS DATETIME)

Upvotes: 0

ChristopheD
ChristopheD

Reputation: 116157

Why not use a computed column? That way you can be sure your year, month, day, hour, min and datetime fields are in sync (if you are obliged to keep the first).

Upvotes: 1

Related Questions