[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3402
  • Last Modified:

Update query to remove time from date/time stamp field in SQL Server table

I have a table in SQL server, in which I have a field called 'date', which has a date/time format. I want to write an update query to leave the date in ther, but remove the time stamp from the field.

So this,

DATE
1/12/2009 1:12:07 AM
1/15/2009 3:45:03 PM

becomes this:

DATE
1/12/2009
1/15/2009

Thanks.
0
super786
Asked:
super786
3 Solutions
 
James MurrellProduct SpecialistCommented:
select convert(varchar(30),getdate(),101)
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
the datetime field stores both date and time, even if you try to store the date alone there, it will append the '00:00;000' as the time

UPDATE urTable
SET urDateColumn = CONVERT(Varchar(8), urDateColumm, 112 )
0
 
SharathData EngineerCommented:

As aneeshattingal said, by default datetime type will have both the date and timestamp values.
You can get rid off the timestamp with CONVERT function for display purpose but it will internally store the timestamp for any date. If no timestamp, then it will be defaulted to 00:00:00
so alter the table to add a column of varchar type, then update newly added column with aneeshattingal solution or any other value which ever format you want.
http://msdn.microsoft.com/en-us/library/ms187928.aspx
0
 
TejasShahMscITCommented:
Hi,

You can also this,
UPDATE      tbl
SET            DateColumn = DATEADD(dd,0, DATEDIFF(dd,0,DateColumn))

This will make date: "2009-07-14 02:00" to "2009-07-14 00:00"

Let me know if it helps you.

Thanks,

Tejas
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now