t-sql replace query 1

[techrob]

Hello,

I have an inventory script running metering active connections on our citrix servers. This script inserts its data into a table with a date and time stamp of the current metering. The date column was set to data type 'date time' resulting in the date being presented in the following manner: 2008-01-15 00:00:00

I tried to fix that by changing the datatype to 'char'. this resulted in the data being converted to: Jan 15 200

The column has now been set to datatype varchar to accept more than 10 characters and 'new' data from the metering is presented in a correct manner: 2008-01-21

But I do need to change all occurrences of ex. 'Jan 15 200' to 2008-01-15. I've looked at the replace command but I didn't quite understand it. So I'm wondering if there's anyone who knows how to solve this problem.

Thankful for all replies

 

[bborissov]

Why you change type?
What's wrong with DateTime?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[techrob]

The script inserts "YYYY-MM-DD" (with no time stamp) into the date coulumn. When it was configured with date time the result was "YYYY-MM-DD 00:00:00" and I just wanted the Date, not the time.

 

[bborissov]

Yes datetime uses time portion also but it is just representation of data. If you want to get rid of time just do this in your front end and keep DateTime.

If you change your field type to varchar you can't imagine the trouble you will have when you want to get data based on that field.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[techrob]

When I tried to change to Date time I got an conversion error, probably because the coulumns contain char's like this "Jan 15 200". Maybe that should be fixed first.

Is there a script/command I could run that would replace All occurences or "Jan 15 200" With "2008-01-15"?

 

[JackVam]

The best thing to do is: keep the column as DateTime and then , either convert on the front end as suggested by bborissov or in your stored procedure , it is possible to do a CONVERT on that column to present the date in a format which relates to your requirements .Check CONVERT in SQL Server BOL

All the IT jobs in one place -

http://www.ITjobfeed.com

 

[gmmastros]

Make sure you have a good backup!

First, run the select to make sure you are only affecting the data you want to.

Select DateColumn,
       Replace(DateColumn, '200', '2008')
From   TableName
Where  DateColumn Like 'Jan%'
       And DateColumn Like '%200'

If everything appears ok, then run this.

Update TableName
Set    DateColumn = Replace(DateColumn, '200', '2008')
Where  DateColumn Like 'Jan%'
       And DateColumn Like '%200'

After you run the update command, I encourage you to change the data type back to date time. When you want to display the data, it would be easy enough to display it in another data format, using the convert statement.

Ex:

Select Convert(VarChar(10), DateColumn, [!]101[/!])
From   TableName

The [!]101[/!] in the previous query specifies the format. There are many other formats to choose from.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[techrob]

Thanks for the code George, that did fix the table. I've changed the datatype back to datetime as suggested.

Thanks for the help.