July 29, 2015 at 1:33 pm
Hello, I am new to this forum and look forward to a long time membership here. I've look at several different methods for removing leading zero's from a column but I need to remove trailing data from a VARCHAR column. For some reason, the old database saved the time along side the date in my client's app.
For example:
The old database format "2015-07-28 00:00:00"
I need the data in this column in the new database to only be the date "2015-07-28", there are alot of rows with this issue.
Is there a query I can run to remove the 00-00-00 from all of the rows? Some of the fields actually have a time in there like this: 2015-07-28 12:15:35, with this one, I don't think it's going to be easy but if I could at least remove the 00-00-00 from all the rows that have it, that would be a good start.
I included a screenshot.
July 29, 2015 at 1:40 pm
Since you're updating the column anyway, also remove the wasteful dashes as well and just store YYYYMMDD.
UPDATE table_name
SET column_name = REPLACE(REPLACE(column_name, '00:00:00', ''), '-', '')
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 29, 2015 at 1:42 pm
Would you be able to convert this column into a date column? That would be a much better option as it will only allow valid dates, remove the time portion and use only 3 bytes per row instead of 10 or more. Dates will also allow date calculations with a lot less effort.
You can also use LEFT( YourColumnName, 10) to get only the date portion, either for SELECTs or an UPDATE.
July 29, 2015 at 1:44 pm
Thank you for such a quick reply. Yes, I can convert it to a date, actually in the new app, I had these columns setup as dates but I had issues with importing so i had to change them back to varchars....
July 29, 2015 at 1:47 pm
sheaS (7/29/2015)
Thank you for such a quick reply. Yes, I can convert it to a date, actually in the new app, I had these columns setup as dates but I had issues with importing so i had to change them back to varchars....
You could use a staging table with varchars to import and validate data and insert to the final table afterwards.
July 29, 2015 at 1:48 pm
sheaS (7/29/2015)
Thank you for such a quick reply. Yes, I can convert it to a date, actually in the new app, I had these columns setup as dates but I had issues with importing so i had to change them back to varchars....
Converting to date would be, IMHO, a better choice.
Another way to convert to just the date would be to first convert to a DATE data type, then back to character. Like:
CONVERT(VARCHAR(10), CONVERT(DATE, [columnName]), 126)
If you're having import problems, you may need to do this first, then it should go right into a column with the DATE data type.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 29, 2015 at 1:50 pm
Well we had a REPLACE and a LEFT solution why not add a CONVERT. 😉
DECLARE @mytable TABLE (myDate VARCHAR(20))
INSERT INTO @mytable
VALUES ('2015-07-28 00:00:00'),('2015-07-29 00:00:00'),('2015-07-30 00:00:00')
SELECT * FROM @mytable
UPDATE @mytable
SET myDate = CONVERT(VARCHAR(10), myDate, 111)
SELECT * FROM @mytable
I agree with Luis, you really should make it a DATE column.
July 29, 2015 at 1:50 pm
I thought the time was needed on the rows where it does appear.
You can directly CAST format 'YYYYMMDD hh:mm[:ss[.sss]]' to a datetime or date.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 29, 2015 at 1:52 pm
Ok, awesome guys, I'll convert it to a date first then run these query's. Thanks a million. Will post results.
July 29, 2015 at 2:01 pm
sheaS (7/29/2015)
Ok, awesome guys, I'll convert it to a date first then run these query's. Thanks a million. Will post results.
If you're going to convert it then you don't actually need to update it provided there are no issues with the data.
CREATE TABLE #mytable (myDate VARCHAR(20))
INSERT INTO #mytable
VALUES ('2015-07-28 00:00:00'),('2015-07-29 00:00:00'),('2015-07-30 00:00:00')
--BEFORE
SELECT * FROM #mytable
ALTER TABLE #mytable ALTER COLUMN myDate DATE
--AFTER
SELECT * FROM #mytable
DROP TABLE #myTable
Make sure to test first!
July 29, 2015 at 2:05 pm
Since I already had the data imported...
1st: I ran this query accordingly....
UPDATE table_name
SET column_name = REPLACE(REPLACE(column_name, '00:00:00', ''), '-', '')
Worked great, my ending result was the dates looked like "20150130" and any row without a date had 00000000.
2nd: I changed the field type to "date" and "20150130" became "2015-01-30" and "00000000" became 0000-00-00 with no problems. Then I converted my input types to "date" on the front end, everything is perfect now.
Thanks a lot for the help guys. I'm member for life.
Happy to contribute in any way I can.
July 29, 2015 at 2:09 pm
yb751 (7/29/2015)
Well we had a REPLACE and a LEFT solution why not add a CONVERT. 😉
DECLARE @mytable TABLE (myDate VARCHAR(20))INSERT INTO @mytable
VALUES ('2015-07-28 00:00:00'),('2015-07-29 00:00:00'),('2015-07-30 00:00:00')
SELECT * FROM @mytable
UPDATE @mytable
SET myDate = CONVERT(VARCHAR(10), myDate, 111)
SELECT * FROM @mytable
I agree with Luis, you really should make it a DATE column.
You conversion won't help as it will only truncate the string. It will basically do the same as the LEFT() function. Another problem it that 111 is not the correct format for this problem and most of the times might not be the best option as it is language dependant.
July 29, 2015 at 2:11 pm
Well to keep it simple....
As "SSCertifiable" said, All I had to do was convert the fields to "date" to get the same result.
July 29, 2015 at 2:27 pm
Luis Cazares (7/29/2015)
You conversion won't help as it will only truncate the string. It will basically do the same as the LEFT() function. Another problem it that 111 is not the correct format for this problem and most of the times might not be the best option as it is language dependant.
I used '111' out of habit as it was a common requirement at my old workplace. As per the OP's requirement it should be '126'. Not sure why my conversion "won't help".
July 30, 2015 at 9:51 am
yb751 (7/29/2015)
Luis Cazares (7/29/2015)
You conversion won't help as it will only truncate the string. It will basically do the same as the LEFT() function. Another problem it that 111 is not the correct format for this problem and most of the times might not be the best option as it is language dependant.
I used '111' out of habit as it was a common requirement at my old workplace. As per the OP's requirement it should be '126'. Not sure why my conversion "won't help".
Format codes don't affect conversions from strings to strings. You can include any value and it will simply ignore it. As you're converting from varchar(20) to varchar(10) which will be implicitly converted back to varchar(20), you're only truncating the string using a double conversion.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply