I need to remove all the times but keep the date in my DateClosed column in SQL server 2008. I can use the query below to strip off 12:00am but not sure how to do it for any time?
UPDATE [tblJobs]
SET [ProjectCompletionDate] = REPLACE([DateClosed], '12:00AM','')
MKozlowski
Member
500 Points
573 Posts
SQL Query to strip off time?
May 16, 2012 02:00 PM|LINK
Hi,
I need to remove all the times but keep the date in my DateClosed column in SQL server 2008. I can use the query below to strip off 12:00am but not sure how to do it for any time?
TabAlleman
All-Star
15557 Points
2698 Posts
Re: SQL Query to strip off time?
May 16, 2012 02:06 PM|LINK
Assuming that both columns are varchar, since if they are datetime columns then there is nothing you can do to strip off the time:
UPDATE tblJobs
SET ProjectCompletionDate = CONVERT(varchar(15), CAST(DateClosed AS DateTime), 101)
whighfield
Star
11721 Points
1859 Posts
Re: SQL Query to strip off time?
May 16, 2012 02:07 PM|LINK
For SQL2008 you can use:
Blog | I need more space:DropBox Referral
MKozlowski
Member
500 Points
573 Posts
Re: SQL Query to strip off time?
May 16, 2012 02:07 PM|LINK
it is an nvarchar column not datetime
TabAlleman
All-Star
15557 Points
2698 Posts
Re: SQL Query to strip off time?
May 16, 2012 02:08 PM|LINK
So use my solution, but replace "varchar" with "nvarchar"
MKozlowski
Member
500 Points
573 Posts
Re: SQL Query to strip off time?
May 16, 2012 02:09 PM|LINK
Tried that then I got
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
TabAlleman
All-Star
15557 Points
2698 Posts
Re: SQL Query to strip off time?
May 16, 2012 02:10 PM|LINK
What does the data in ClosedDate look like?
MKozlowski
Member
500 Points
573 Posts
Re: SQL Query to strip off time?
May 16, 2012 02:11 PM|LINK
3/16/2011 3:59:34 PM, Maybe strip off characters to the right somehow?
TabAlleman
All-Star
15557 Points
2698 Posts
Re: SQL Query to strip off time?
May 16, 2012 02:13 PM|LINK
Yes, use CHARINDEX to find the location of the space character, and LEFT to select only the characters to the left of it.
kedarrkulkar...
All-Star
34045 Points
5474 Posts
Re: SQL Query to strip off time?
May 16, 2012 02:21 PM|LINK
try this
UPDATE [tblJobs] SET[ProjectCompletionDate] = SUBSTRING([DateClosed],0,CHARINDEX(' ',[DateClosed],0)-1)
hope this helps...
KK
Please mark as Answer if post helps in resolving your issue
My Site