CODEWITHSUNDEEP
sqlsql-serverdatetime
Tiago
Tiago

Reputation: 1991

How to update all rows to truncate milliseconds

I have a lookup table where one of the columns contains each date between 2000 and 2030.

Problem is that the generated dates here all have milliseconds at the end, eg:

2000-01-01 00:00:00.000
2000-01-02 00:00:00.000
2000-01-03 00:00:00.000
2000-01-04 00:00:00.000

My other datetime columns in my data don't have this, e.g.:

2011-05-17 13:11:11
2011-05-18 09:39:17
2011-06-15 10:54:09
2011-06-20 10:16:43

http://s15.postimg.org/5jw1q6y2z/2014_03_14_09_47_48_Microsoft_SQL_Server_Managem.png

I think this may be causing an issue when aggregating up to Month using a BI tool, so I wanted to update all rows in the Calendar_Date column (in the lookup table), to truncate milliseconds off all rows. Could someone provide guidance on how I can do this?

Structures of both columns: enter image description here enter image description here

Thanks in advance!

Upvotes: 0

Views: 741

Answers (4)

Jodrell
Jodrell

Reputation: 35766

Try this query,

SELECT
    a12.Calendar_Year Calendar_Year,
    (sum(a11.mc_gross) - sum(a11.mc_fee)) WJXBFS1
FROM shine_orders a11
JOIN Calendar a12
    ON CONVERT(date, a11.dt) = CONVERT(date, a12.Calendar_Date)
GROUP BY a12.Calendar_Year

If Calendar_Year is just how it sounds, a calendar year, you could do

SELECT
    Year(a11.dt) Calendar_Year,
    (sum(a11.mc_gross) - sum(a11.mc_fee)) WJXBFS1
FROM shine_orders a11
GROUP BY Year(a11.dt)

Upvotes: 0

MatBailie
MatBailie

Reputation: 86808

I believe that you have mis-disagnosed the problem, it's not the data, it appears to be the SQL.

Let me show you some issues with your query.

SELECT
  a12.Calendar_Year   AS Calendar_Year,
  a11.dt              AS datetime,
  (sum(a11.mc_gross) - sum(a11.mc_fee))   AS WJXBFS1
FROM
  shine_orders   AS a11
JOIN
  Calendar       AS a12
    ON (  CONVERT(DATETIME, CONVERT(VARCHAR(10), a11.dt,            101))
        = CONVERT(DATETIME, CONVERT(VARCHAR(10), a12.Calendar_Date, 101))
        AND a11.dt = a12.Calendar_Date
       )
GROUP BY
  a12.Calendar_Year,
  a11.dt

That's your query slightly differently laid out so that I can identify individual pieces.


Let's look at the JOIN first...

ON (  CONVERT(DATETIME, CONVERT(VARCHAR(10), a11.dt,            101))
    = CONVERT(DATETIME, CONVERT(VARCHAR(10), a12.Calendar_Date, 101))

This does indeed compare date parts only. It converts both values to strings of the format 'mm/dd/yyyy' and then compares them. It's not considered the most efficient way of doing it, but it does work.

    AND a11.dt = a12.Calendar_Date

This seems to be a rogue condition. This compares values that include a time, to values that don't. this will be preventing your join from working.


Now let's look at the SELECT and the GROUP BY

SELECT
  a12.Calendar_Year   AS Calendar_Year,
  a11.dt              AS datetime,

and

GROUP BY
  a12.Calendar_Year,
  a11.dt

a11.dt, is actually the value from the data, not the calendar table. This means that you're not grouping by day, you're grouping by the exact day and time that exists in the data.


I would recommend the following query instead.

SELECT
  a12.Calendar_Year   AS Calendar_Year,
  a12.Calendar_Date   AS Calendar_Date,
  (sum(a11.mc_gross) - sum(a11.mc_fee))   AS WJXBFS1
FROM
  Calendar       AS a12
LEFT JOIN
  shine_orders   AS a11
    ON  a11.dt >= a12.Calendar_Date
    AND a11.dt <  a12.Calendar_Date + 1
WHERE
      a12.Calendar_Date >= '2013-01-01'
  AND a12.Calendar_Date <  '2014-01-01'
GROUP BY
  a12.Calendar_Year,
  a12.Calendar_Date

EDIT: I originally missed out a + 1 in the final query.

Upvotes: 1

M.Ali
M.Ali

Reputation: 69594

When you say DATETIME data type it means sql server will store the values as two binary numbers; one representing the number of days (the date) and the other representing the number of ticks (the time) and the data is presented as 'YYYY-MM-DD hh:mm:ss.000'. If it is stored in any other way other then this it is not datetime.

To store datetime values in your required format which is 'YYYY-MM-DD hh:mm:ss' you will need to store them as string as varchar values.

But the question is ? is it really worth it ? And the answer is NOOOOOO...

Reasons

1) If you want to present data in 'YYYY-MM-DD hh:mm:ss' format you can always do this at rumtime, writing queries which will present the data in this format.

2) If you have saved your values as string you will need to cast them as datetime before you could use any of the datetime functions.

3) Since these will be only string not datetime if you do not have proper constraint user can add false data such as 2013-15-15. and sql server will accept it as a valid string.

and the list goes on. thats why I would suggest to keep the extra zeros and dont worry abot them.

UPDATE

Your Current column could be in SMALLDATETIME Datatype be not in Datetime.

Try this....

CREATE TABLE Test_Table(Col1 SMALLDATETIME, Col2 DATETIME)
GO

INSERT INTO Test_Table
VALUES 
(GETDATE()-1,GETDATE()-1),(GETDATE()-2,GETDATE()-2),(GETDATE()-3,GETDATE()-3)

SELECT * FROM Test_Table

Result Set

╔═════════════════════╦═════════════════════════╗
║        Col1         ║          Col2           ║
╠═════════════════════╬═════════════════════════╣
║ 2014-03-13 10:16:00 ║ 2014-03-13 10:15:58.483 ║
║ 2014-03-12 10:16:00 ║ 2014-03-12 10:15:58.483 ║
║ 2014-03-11 10:16:00 ║ 2014-03-11 10:15:58.483 ║
╚═════════════════════╩═════════════════════════╝

Upvotes: 0

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11609

update table
set Calendar_Date=convert(datetime,(convert(date,Calendar_Date)))

Upvotes: 1

Related Questions