Solved

Clean MSSQL database...

Posted on 2010-09-20
17
205 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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
 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

This article will show, step by step, how to integrate R code into a R Sweave document
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.

785 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