Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Clean MSSQL database...

Posted on 2010-09-20
17
Medium Priority
?
219 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 143

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 143

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 143

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 143

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 143

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 143

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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Suggested Courses

824 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