Solved

Clean MSSQL database...

Posted on 2010-09-20
17
210 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…

617 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