Updating values in SQL Server database with MVC

Question

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:

Year int = 12
Month int = 4
Day int = 27
Hour int = 12
Min int = 0

New column: smalldatetime

combinedDate = '2012-04-27 12:00:00 PM'

SQL = SQL Server ?? SQL really is only the Structured Query Language - not a database product... — marc_s
– marc_s, Commented Apr 27, 2012 at 19:30

Shelakel · Accepted Answer · 2012-04-27 19:34:52Z

1

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.

answered Apr 27, 2012 at 19:34

Shelakel

1,0709 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

marc_s Over a year ago

Your DATEPARTs are not entirely correct - you should have: Year as (DATEPART(YEAR, Time)) persisted not null - first parameter is just the definition of the part, without any quotes

ChristopheD · Accepted Answer · 2012-04-27 19:22:41Z

1

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).

answered Apr 27, 2012 at 19:22

ChristopheD

117k30 gold badges167 silver badges182 bronze badges

1 Comment

ExceptionLimeCat Over a year ago

So how do you convert these int columns to one combine datetime?

user756519 · Accepted Answer · 2012-04-27 20:55:53Z

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

thekaveman · Accepted Answer · 2012-04-27 19:34:23Z

0

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)

edited Apr 27, 2012 at 19:34

answered Apr 27, 2012 at 19:23

thekaveman

4,42528 silver badges45 bronze badges

2 Comments

marc_s Over a year ago

That very useful function is unfortunately available with SQL Server 2012 only - not with any version before...

marc_s Over a year ago

Another thing: the format YY-MM-DD to create a DATETIME in SQL Server is not safe - e.g. it doesn't work under all language and dateformat settings. If you want to create a DATETIME you need to use either YYYYMMDD (without any dashes), or then YYYY-MM-DDTHH:MM:SS to make this work under all configurations.

Collectives™ on Stack Overflow

Updating values in SQL Server database with MVC

4 Answers 4

1 Comment

1 Comment

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related