Reputation: 1991
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
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:
Thanks in advance!
Upvotes: 0
Views: 741
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
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
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
Reputation: 11609
update table
set Calendar_Date=convert(datetime,(convert(date,Calendar_Date)))
Upvotes: 1