Mark Wilson
asked on
Time part of datetime to be the same
SQL 2008
I have data in a datetime field (examples as follows)
2014-10-26 00:00:00.000
2014-10-29 01:00:00.000
2014-10-29 02:00:00.000
2014-10-29 00:00:00.000
I want the time part to be 00:00:00.000 for every date in the field
So the data would be
2014-10-26 00:00:00.000
2014-10-29 00:00:00.000
2014-10-29 00:00:00.000
2014-10-29 00:00:00.000
Thanks
I have data in a datetime field (examples as follows)
2014-10-26 00:00:00.000
2014-10-29 01:00:00.000
2014-10-29 02:00:00.000
2014-10-29 00:00:00.000
I want the time part to be 00:00:00.000 for every date in the field
So the data would be
2014-10-26 00:00:00.000
2014-10-29 00:00:00.000
2014-10-29 00:00:00.000
2014-10-29 00:00:00.000
Thanks
try this
this will chnage your datetime to date with time 00000
SELECT getdate(),dateadd(ms,-1*DA
this will chnage your datetime to date with time 00000
SELECT getdate(),dateadd(ms,-1*DA
>I want the time part to be 00:00:00.000 for every date in the field
I think we've nailed the conversion part of this question, so if you could further define what you mean by the above (select, change, ...) we're home.
I think we've nailed the conversion part of this question, so if you could further define what you mean by the above (select, change, ...) we're home.
The command below will reset the time back to 00:00:00.000 (midnight).
UPDATE table_name
SET datetime_column = DATEADD(DAY, DATEDIFF(DAY, 0, datetime_column), 0)
Or you use that to strip the time in a SELECT list:
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, datetime_column), 0) AS datetime, ...
FROM table_name
WHERE ...
UPDATE table_name
SET datetime_column = DATEADD(DAY, DATEDIFF(DAY, 0, datetime_column), 0)
Or you use that to strip the time in a SELECT list:
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, datetime_column), 0) AS datetime, ...
FROM table_name
WHERE ...
IF you are looking for ways to accurately filter data by a date range, then you do NOT have to alter your data to suit the range criteria. Instead alter the criteria to suit the data.
your_table
dt_created
2014-10-26 00:00:00.000
2014-10-29 01:00:00.000
2014-10-29 02:00:00.000
2014-10-29 00:00:00.000
Let's say you want all transactions on 2014-10-29, so:
select *
from your_table
where ( dt_created >= '20141029' and dt_created < '20141030' )
result:
dt_created
2014-10-29 01:00:00.000
2014-10-29 02:00:00.000
2014-10-29 00:00:00.000
what you should NOT do is either of these (as they will be slower):
For more on this topic please see: "Beware of Between"
your_table
dt_created
2014-10-26 00:00:00.000
2014-10-29 01:00:00.000
2014-10-29 02:00:00.000
2014-10-29 00:00:00.000
Let's say you want all transactions on 2014-10-29, so:
select *
from your_table
where ( dt_created >= '20141029' and dt_created < '20141030' )
result:
dt_created
2014-10-29 01:00:00.000
2014-10-29 02:00:00.000
2014-10-29 00:00:00.000
what you should NOT do is either of these (as they will be slower):
where CAST(dt_created AS DATE) = '20141029'
where DATEADD(DAY, DATEDIFF(DAY, 0, dt_created), 0) = '20141029'
This is not to suggest that the answers before this are incorrect! Both the cast() or dateadd() approaches work for the suggestion actions. I am only discussing filtering by a date range.
where DATEADD(DAY, DATEDIFF(DAY, 0, dt_created), 0) = '20141029'
This is not to suggest that the answers before this are incorrect! Both the cast() or dateadd() approaches work for the suggestion actions. I am only discussing filtering by a date range.
For more on this topic please see: "Beware of Between"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
halifaxman, do you still need help on this question?
Open in new window
Or just change the column data type to date
Open in new window
>I want the time part to be 00:00:00.000 for every date in the field
You'll need to be more specific. Change the value? Change the data type? One time deal but leave alone the ability to have other datetime values? Is this column datetime, or something like varchar?