Solved

Clean MSSQL database...

Posted on 2010-09-20
17
207 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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 additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.
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.

680 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