How do I get rid of time in the date filed

Question

How do I get rid of time in the date filed

Sara-657226

Valued Member

Points: 59
More actions
March 5, 2008 at 9:43 am

#179559

Hello,
My question is how I can get rid of the time in date filed. This query is scheduled to run every night and push the data to the Backlog_Daily table
INSERT INTO m2mqueries..Backlog_Daily
SELECT GETDATE() AS DataDate, SUM(sorels.funetprice *
(soitem.fquantity - (sorels.fshipbook + sorels.fshipbuy + sorels.fshipmake))) AS BackOrderTotal
FROM sorels INNER JOIN soitem
ON sorels.fsono = soitem.fsono AND sorels.finumber = soitem.finumber
INNER JOIN somast
ON soitem.fsono = somast.fsono
WHERE (sorels.forderqty > sorels.fshipbook + sorels.fshipbuy + sorels.fshipmake)
AND (somast.fstatus = 'Open')
This is the result and everyday I have to run the query and delete the time to be able to update my report. I need to add statement to my query to clean the date then insert it to the table
DataDate BackOrderTotal
3/2/2008 10.00
3/3/2008 16.45
3/3/2008 8:00:01Pm 12.91

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

Keeping in mind that datetime fields always store both date and time components no matter what (at least until you get to SQL Server 2008 with its new data types) - it sounds like you're looking to zero out the time values.

I would try something like this:

update MyTable

set DataDate=dateadd(day,datediff(day,0,DateDiff),0)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Mohammad Mazharuddin Ehsan SSCarpal Tunnel Points: 4552 More actions · Answer 2

You can make a function and use it in your insert statement as below. This way you will avaoid the update statement.

CREATE function [DateOnly](@DateTime DateTime)

-- Returns the DateTime minus the time portion

returns datetime

as

begin

return dateadd(dd, datediff(dd,0,@DateTime), 0)

end

INSERT INTO m2mqueries..Backlog_Daily

SELECT dbo.DateOnly(GETDATE()) AS DataDate, SUM(sorels.funetprice *

(soitem.fquantity - (sorels.fshipbook + sorels.fshipbuy + sorels.fshipmake))) AS BackOrderTotal

FROM sorels INNER JOIN soitem

ON sorels.fsono = soitem.fsono AND sorels.finumber = soitem.finumber

INNER JOIN somast

ON soitem.fsono = somast.fsono

WHERE (sorels.forderqty > sorels.fshipbook + sorels.fshipbuy + sorels.fshipmake)

AND (somast.fstatus = 'Open')

-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

TraderSam SSCrazy Points: 2931 More actions · Answer 3

Or just use Matt's solution in the SELECT...

SELECT dateadd(day,datediff(day,0,getdate()),0) AS DataDate, ....

If it was easy, everybody would be doing it!;)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

There's usually some merit and some future need for knowing the times. I think it's a huge loss of data to update datetimes that have the time to all having a 0 time. My recommendation is to add a caclulated column that strips the time. It requires absolutely no maintanence and, properly constructed, can be indexed.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TraderSam SSCrazy Points: 2931 More actions · Answer 5

Or if you just need the date only for a report, use this in the SELECT query in your report...

select convert(varchar(10), DataDate, 101)...

...and leave the data as is.

If it was easy, everybody would be doing it!;)