Replace a / with a -

Question

0.00/5 (No votes)

See more:

Hi

I want to run a query to replace the values in my date fields.
Currently it is as 2013/12/02 01:12:49:856 and is stored as varchar in the database
I want to replacce the / with a - on all the records
I have tried the following script

SQL

UPDATE AuditResults
SET lastupdated = REPLACE(lastupdated, N'/', N'-')

and

SQL

UPDATE AuditResults
SET lastupdated = REPLACE(lastupdated, '/', '-')

But this does not work.
Any other suggestions?

Thank you in advance

Posted 5-Dec-13 0:26am

Member 9374423

Add a Solution

Comments

phil.o 5-Dec-13 6:34am

Just my 2 cents: datetimes should be stored in the database with the appropriate datatype. Using varchar/nvarchar to hold datetime values is a quite bad pratice.

Member 9374423 5-Dec-13 6:38am

I was not part of the initial developer team, I know this,but there's nothing i can do about that now, haha

phil.o 5-Dec-13 7:43am

As I said, this was just my 2 cents about that; you are going to meet numerous kinds of troubles when you will have to do comparisons; and I'm even not talking about performance issues. Good luck with this inherited project :)

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Ganesh Raja · Accepted Answer · 2013-12-05T00:44:00

Solution 1

You can convert and update the records,

SQL

SELECT CONVERT(VARCHAR(23), GETDATE(), 121)

SQL

UPDATE AuditResults
SET lastupdated =CONVERT(VARCHAR(23), lastupdated , 121)

Reference:

Posted 5-Dec-13 0:44am

Ganesh Raja

Updated 5-Dec-13 0:47am

v2

Comments

Member 9374423 5-Dec-13 6:46am

I need to update the fields, not run a select

Ganesh Raja 5-Dec-13 6:48am

Can u pls check conversion in my code

Member 9374423 5-Dec-13 7:48am

HiThis is perfect thank you!!!!!!!!!!!

OriginalGriff · Accepted Answer · 2013-12-05T00:47:00

Solution 2

phil.o is right - storing datetime values as string is a spectacularly stupid decision, and should be changed (and the person who decided on it thumped round the head with something solid).

But...I just checked here and both versions worked fine, so I suspect your code rather than anything else. Did you add a WHERE clause that you didn't tell us about?

Posted 5-Dec-13 0:47am

OriginalGriff

Comments

Member 9374423 5-Dec-13 6:49am

Hi, I do realize this, but the change to that is too significant.
Then the where clause, I have tried with and without a where clause. Both do not work.
Please can you add the code you tried?
Kind regards

OriginalGriff 5-Dec-13 6:53am

It's the same as your, with a different table and column name:
UPDATE Product SET Product_Name = REPLACE(Product_Name, N'/', N'-')
and
UPDATE Product SET Product_Name = REPLACE(Product_Name, '/', '-')
both work fine in SSMS...

Member 9374423 5-Dec-13 7:03am

This does not work, are there any other solutions for this?

OriginalGriff 5-Dec-13 7:10am

As I said - it does here.
Show us exactly what you are doing. Copy and paste from your code.

Member 9374423 5-Dec-13 7:11am

I did in the initial file, that's what I'm using

OriginalGriff 5-Dec-13 7:16am

Clearly not - because it does work here, so there has to be a difference between your setup and mine.
I just typed it into a new query in SSMS and ran it against some test data set with values copied from your question. And when I ran it, the slashes changed to hyphens.

So what are you doing that is different?

Member 9374423 5-Dec-13 7:40am

Griff, I ran that code almost 50 times now, exactly as is, on a string field and a datetime field, and it does not work.
If I tell you I am running the code then that's what I'm doing, why would I post an issue and then give the wrong details of what I am doing, I need the issue resolved so i gave you all the details, so we can resolve this.

OriginalGriff 5-Dec-13 7:51am

I'm not saying you haven't, or that you are lying - I'm just saying that when I do exactly the same thing here (apparently) it works fine. So we need to know exactly what the difference is between how you are doing it and how I am doing it if we are to resolve this! :laugh:

Member 9374423 5-Dec-13 6:50am

The reason is that the varchar field could be any number of values, from names, to dates and so forth

OriginalGriff 5-Dec-13 6:54am

That is an even sillier decision! How are you supposed to know what to do with the data if you don't know what the content is meant to be? (And how you you know it doesn't contain
x = y / z
at some point?)

Member 9374423 5-Dec-13 6:55am

They have type ID's

OriginalGriff 5-Dec-13 7:08am

So your query needs to have a WHERE clause which checks the type ID.
And it's still a stupid decision, because to use the content, every query needs to check the type id...

Member 9374423 5-Dec-13 7:38am

Ok again it was not my idea, please guys can we focus on the issue here, I was not part of the setup. but I have the issue now.
I have tried the where clause and also tried it without the where clause and it does not work.
I have mentioned this before.
Can anyone please just tell me if there is another way?