Update DateTime field to Date only

29 Jun 2006 11:01 AM

Peter Hartlén

I want to update the DateTime field of a table to contain only Date
information. The query below works fine but I wonder if there are better
solutions to this?

Example:
Old values:
[id]    [inDate]
ID1   2006-06-27 06:03:23.230
ID2   2006-06-28 09:03:23.230

New values:
[id]    [inDate]
ID1   2006-06-27 00:00:00.000
ID2   2006-06-28 00:00:00.000

Query I am using (Northwind database)
-- First update a date to contain time as well
UPDATE Orders SET OrderDate = '19960722 08:51:43' WHERE OrderID = 10263

-- display the result
SELECT OrderID,OrderDate FROM Orders

-- This is the main function that updates all datetimes to contain only date
UPDATE tbl1
SET tbl1.OrderDate = (SELECT DATEADD(d,DATEDIFF(d,0,tbl2.OrderDate),0))
FROM orders tbl1, orders tbl2
WHERE tbl1.OrderID = tbl2.OrderID

-- display the result
SELECT OrderID,OrderDate FROM Orders

Thanks!

/ Peter

29 Jun 2006 12:08 PM

Razvan Socol

Peter Hartlén wrote:

> I want to update the DateTime field of a table to contain only Date
> information. The query below works fine but I wonder if there are better
> solutions to this?
> [...]
> UPDATE tbl1
> SET tbl1.OrderDate = (SELECT DATEADD(d,DATEDIFF(d,0,tbl2.OrderDate),0))
> FROM orders tbl1, orders tbl2
> WHERE tbl1.OrderID = tbl2.OrderID
> [...]

There is no need to specify the Orders table twice. Simply use:
UPDATE orders
SET OrderDate = DATEADD(d,DATEDIFF(d,0,OrderDate),0)

Razvan

29 Jun 2006 12:20 PM

Peter Hartlén

Tada!

Thanks!

"Razvan Socol" <rso***@gmail.com> skrev i meddelandet
news:1151582911.669721.299260@j72g2000cwa.googlegroups.com...

Peter Hartlén wrote:

> I want to update the DateTime field of a table to contain only Date
> information. The query below works fine but I wonder if there are better
> solutions to this?
> [...]
> UPDATE tbl1
> SET tbl1.OrderDate = (SELECT DATEADD(d,DATEDIFF(d,0,tbl2.OrderDate),0))
> FROM orders tbl1, orders tbl2
> WHERE tbl1.OrderID = tbl2.OrderID
> [...]

There is no need to specify the Orders table twice. Simply use:
UPDATE orders
SET OrderDate = DATEADD(d,DATEDIFF(d,0,OrderDate),0)

Razvan

29 Jun 2006 1:14 PM

Peter Hartlén

Another question, is it possible to list all DateTime fields that contains
time information?

Thanks,

Peter

Show quote

"Peter Hartlén" <pe***@data.se> skrev i meddelandet
news:u%23%232aZ3mGHA.3372@TK2MSFTNGP03.phx.gbl...
> Tada!
>
> Thanks!
>
>
> "Razvan Socol" <rso***@gmail.com> skrev i meddelandet
> news:1151582911.669721.299260@j72g2000cwa.googlegroups.com...
> Peter Hartlén wrote:
>> I want to update the DateTime field of a table to contain only Date
>> information. The query below works fine but I wonder if there are better
>> solutions to this?
>> [...]
>> UPDATE tbl1
>> SET tbl1.OrderDate = (SELECT DATEADD(d,DATEDIFF(d,0,tbl2.OrderDate),0))
>> FROM orders tbl1, orders tbl2
>> WHERE tbl1.OrderID = tbl2.OrderID
>> [...]
>
> There is no need to specify the Orders table twice. Simply use:
> UPDATE orders
> SET OrderDate = DATEADD(d,DATEDIFF(d,0,OrderDate),0)
>
> Razvan
>
>

29 Jun 2006 1:23 PM

Peter Hartlén

I found a sloution myself:

select OrderDate from Orders where OrderDate <>
DATEADD(d,DATEDIFF(d,0,OrderDate),0)

/ Peter

Show quote

"Peter Hartlén" <pe***@data.se> skrev i meddelandet
news:OtDcn33mGHA.1852@TK2MSFTNGP03.phx.gbl...
> Another question, is it possible to list all DateTime fields that contains
> time information?
>
> Thanks,
>
> Peter
>
>
>

29 Jun 2006 3:44 PM

Arnie Rowland

You could cycle through the tables (perhaps using a list generated from INFORMATION_SCHEMA.COLUMNS), first looking at columns with datetime datatype, and then looking for columns with non-zero (midnight) time.

SELECT
TABLE_NAME
, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'datetime'

--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."

*Yet Another Certification Exam

Show quote

"Peter Hartlén" <pe***@data.se> wrote in message news:eSLk483mGHA.2256@TK2MSFTNGP04.phx.gbl...
>I found a sloution myself:
>
> select OrderDate from Orders where OrderDate <>
> DATEADD(d,DATEDIFF(d,0,OrderDate),0)
>
> / Peter
>
>
> "Peter Hartlén" <pe***@data.se> skrev i meddelandet
> news:OtDcn33mGHA.1852@TK2MSFTNGP03.phx.gbl...
>> Another question, is it possible to list all DateTime fields that contains
>> time information?
>>
>> Thanks,
>>
>> Peter
>>
>>
>>
>
>

29 Jun 2006 4:24 PM

BurgerKING

this uses simple casting without any calculation...

update table
set datetimecolumn = cast(cast(datetimecolumn as varchar(11)) as
datetime)

Peter Hartlén wrote:
Show quote

> I want to update the DateTime field of a table to contain only Date
> information. The query below works fine but I wonder if there are better
> solutions to this?
>
> Example:
> Old values:
> [id]    [inDate]
> ID1   2006-06-27 06:03:23.230
> ID2   2006-06-28 09:03:23.230
>
> New values:
> [id]    [inDate]
> ID1   2006-06-27 00:00:00.000
> ID2   2006-06-28 00:00:00.000
>
> Query I am using (Northwind database)
> -- First update a date to contain time as well
> UPDATE Orders SET OrderDate = '19960722 08:51:43' WHERE OrderID = 10263
>
> -- display the result
> SELECT OrderID,OrderDate FROM Orders
>
> -- This is the main function that updates all datetimes to contain only date
> UPDATE tbl1
> SET tbl1.OrderDate = (SELECT DATEADD(d,DATEDIFF(d,0,tbl2.OrderDate),0))
> FROM orders tbl1, orders tbl2
> WHERE tbl1.OrderID = tbl2.OrderID
>
> -- display the result
> SELECT OrderID,OrderDate FROM Orders
>
> Thanks!
>
> / Peter