Solved

Clean MSSQL database...

Posted on 2010-09-20
17
204 Views
Last Modified: 2012-06-27
Hello

I need som  help..
 I have a column in my database that I would like to fix.. I dont´like the old data I have in the database..

It´s like this...

DateTime
2010-09-07 09:38:28.773


So what I would like to do is loop trogh the whole database and remove  "09:38:28.773"
so only "2010-09-07" is left in the column...

Can I clean my databse like that?

0
Comment
Question by:kavvis
  • 7
  • 7
  • 2
  • +1
17 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33715408
what is the data type of the column?

if you are really in the sql 2008, you might check out the new data types:
http://msdn.microsoft.com/en-us/library/ms186724.aspx

0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33715428
See remark on angelIII.
If you are programming against the database, you can format the date into the code (or in the query) the way you want it.

Example (c#): Custom Date and Time Format Strings
http://msdn.microsoft.com/en-us/library/8kb3ddd4.aspx

Example (sqlserver): Date Formats
http://www.sql-server-helper.com/tips/date-formats.aspx
0
 

Author Comment

by:kavvis
ID: 33715449
the data  column is  "nvarchar(MAX)"

And  it just now I wolud like to do it.. not all the time.. I just want to clean the database now and still have the old data..

So I just want to remove the 7 last chars..


I have tried something like this with no luck..


UPDATE TEST
SET DatumSUBSTR(TEST.Datum, 3)
WHERE Datum = '2010-09-13 02:00:00'





0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33715502
UPDATE TEST
SET Datum = SUBSTRING(Datum, 0,10)
--WHERE Datum = '2010-09-13 02:00:00'
0
 

Author Comment

by:kavvis
ID: 33715536
OK! Thank you..

Hmm... but I was to dum to understand that MSSQL is pretty clever..

I change the datetyp to date then it automatik removde   the time ifrån the date colum..

and the same happent do the time column.. just changed the datatype to Time  then it removde the date  autoamtik!
¨

but I got the problem with "13:15:57.0000"    I don´t like that

I just want to have  13:15:57   but this is a setting in the database?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33715555
so you want to do this:


UPDATE TEST

SET DatumSUBSTR(TEST.Datum, 10)

WHERE Datum like '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]%

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33715560
>I just want to have  13:15:57   but this is a setting in the database?

no.
0
 

Author Comment

by:kavvis
ID: 33715593
hmm...
TEST = tabelname...

UPDATE TEST
SET DatumSUBSTR(TEST.TidSlut, 10)
WHERE TidSlut like '[0-9]%


should this work?    because I get errors...

"Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ','.
Msg 105, Level 15, State 1, Line 6
Unclosed quotation mark after the character string '[0-9]%"
0
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33715609
sorry, forgot the closing '
UPDATE TEST

SET DatumSUBSTR(TEST.Datum, 10)

WHERE Datum like '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]%'

Open in new window

0
 

Author Comment

by:kavvis
ID: 33715648
hmm.. Have I understand this correct..


UPDATE TEST
SET DatumSUBSTR(TEST.TidSlut, 10)
WHERE TidSlut like '[0-9][0-9]:[0-9][0-9]:[0-9][0-9][0-9]%'

18:20:00.000  I want to have -> 18:20:00


But this don´t work either...

error

Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ','.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33715663
for the second part:


UPDATE TEST

SET DatumSUBSTR(TEST.TidSlut, 8)

WHERE TidSlut like '[0-9][0-9]:[0-9][0-9]:[0-9][0-9]%'

Open in new window

0
 

Author Comment

by:kavvis
ID: 33715678
I have tested that allready...  I get the error on this row  SET DatumSUBSTR(TEST.TidSlut, 8)

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33715693
sorry, missing the =
UPDATE TEST

SET Datum = SUBSTR(TEST.TidSlut, 8)

WHERE TidSlut like '[0-9][0-9]:[0-9][0-9]:[0-9][0-9]%'

Open in new window

0
 

Author Comment

by:kavvis
ID: 33715728
almost...

SUBSTR' is not a recognized built-in function name.


that problem I get now... shit it feels like we are so close a sloution.. :D  come on now :D  more tips ;)
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 33715800
well, indeed, if you copy only half of the things ..
and as I am using not only mssql, but also others, I don't see the issues immediately...


please use SUBSTRING instead of SUBSTR
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 33715805
<<SUBSTR' is not a recognized built-in function name.>>
 I believe he meant what angelIII meant is *substring* (pls no points)

HTH
0
 

Author Closing Comment

by:kavvis
ID: 33715959
thank y ou all!

UPDATE TEST
SET Tid = SUBSTRING(TEST.Tid, 12,9)
WHERE Tid like '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9]%'


There I get it!!!  thank you!
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now