October 18, 2018 at 10:04 am
I want to convert data that is currently in a varchar column as "7/30/2016" to a new column as a date in format mm/dd/yyyy
how can i do this?
October 18, 2018 at 10:07 am
GrassHopper - Thursday, October 18, 2018 10:04 AMI want to convert data that is currently in a varchar column as "7/30/2016" to a new column as a date in format mm/dd/yyyy
how can i do this?
If you want to convert to a date type column:
select convert(date,'7/30/2016',101)
or if you just want a string with that format:select convert(varchar,convert(date,'7/30/2016',101),101)
October 18, 2018 at 10:12 am
GrassHopper - Thursday, October 18, 2018 10:04 AMI want to convert data that is currently in a varchar column as "7/30/2016" to a new column as a date in format mm/dd/yyyy
how can i do this?
Dates do not have 'formats' in SQL Server. It is the job of the presentation tool to format dates with a DATE datatype in whatever way is required.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 18, 2018 at 10:53 am
Jonathan AC Roberts - Thursday, October 18, 2018 10:07 AMGrassHopper - Thursday, October 18, 2018 10:04 AMI want to convert data that is currently in a varchar column as "7/30/2016" to a new column as a date in format mm/dd/yyyy
how can i do this?If you want to convert to a date type column:
select convert(date,'7/30/2016',101)
or if you just want a string with that format:select convert(varchar,convert(date,'7/30/2016',101),101)
Actually, I want my string "7/30/2016" to convert to 30/7/2016
October 18, 2018 at 11:00 am
GrassHopper - Thursday, October 18, 2018 10:53 AMJonathan AC Roberts - Thursday, October 18, 2018 10:07 AMGrassHopper - Thursday, October 18, 2018 10:04 AMI want to convert data that is currently in a varchar column as "7/30/2016" to a new column as a date in format mm/dd/yyyy
how can i do this?If you want to convert to a date type column:
select convert(date,'7/30/2016',101)
or if you just want a string with that format:select convert(varchar,convert(date,'7/30/2016',101),101)Actually, I want my string "7/30/2016" to convert to 30/7/2016
Any chance you can fix the architecture and store dates as the date datatype? It makes so many things a lot easier to choose the correct datatype. We don't always have that luxury though, we sometimes have to deal with the poor decisions of others. If you can store the data in the correct datatype this is super simple. You send the date to the front end and let the presentation layer deal with formatting where it best suited. Once you get this first converted to a date you can use CONVERT to make it output in a number of formats. https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 18, 2018 at 11:01 am
GrassHopper - Thursday, October 18, 2018 10:53 AMJonathan AC Roberts - Thursday, October 18, 2018 10:07 AMGrassHopper - Thursday, October 18, 2018 10:04 AMI want to convert data that is currently in a varchar column as "7/30/2016" to a new column as a date in format mm/dd/yyyy
how can i do this?If you want to convert to a date type column:
select convert(date,'7/30/2016',101)
or if you just want a string with that format:select convert(varchar,convert(date,'7/30/2016',101),101)Actually, I want my string "7/30/2016" to convert to 30/7/2016
Convert your current varchar dates into proper dates and store them as dates. then when you extract them, the UI/Report can format them any way they want.
October 18, 2018 at 11:37 am
Sean Lange - Thursday, October 18, 2018 11:00 AMGrassHopper - Thursday, October 18, 2018 10:53 AMJonathan AC Roberts - Thursday, October 18, 2018 10:07 AMGrassHopper - Thursday, October 18, 2018 10:04 AMI want to convert data that is currently in a varchar column as "7/30/2016" to a new column as a date in format mm/dd/yyyy
how can i do this?If you want to convert to a date type column:
select convert(date,'7/30/2016',101)
or if you just want a string with that format:select convert(varchar,convert(date,'7/30/2016',101),101)Actually, I want my string "7/30/2016" to convert to 30/7/2016
Any chance you can fix the architecture and store dates as the date datatype? It makes so many things a lot easier to choose the correct datatype. We don't always have that luxury though, we sometimes have to deal with the poor decisions of others. If you can store the data in the correct datatype this is super simple. You send the date to the front end and let the presentation layer deal with formatting where it best suited. Once you get this first converted to a date you can use CONVERT to make it output in a number of formats. https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017
That's what I want to do...
Getting closer. When I try the line below...it works on top 5000 files.
Case When [Date_from] <> '' Then Convert(varchar,Convert(date,[date_from], 101), 103) end as Date_from2
But when i run it on the whole table, I get this :
Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.
October 18, 2018 at 12:00 pm
GrassHopper - Thursday, October 18, 2018 11:37 AMSean Lange - Thursday, October 18, 2018 11:00 AMGrassHopper - Thursday, October 18, 2018 10:53 AMJonathan AC Roberts - Thursday, October 18, 2018 10:07 AMGrassHopper - Thursday, October 18, 2018 10:04 AMI want to convert data that is currently in a varchar column as "7/30/2016" to a new column as a date in format mm/dd/yyyy
how can i do this?If you want to convert to a date type column:
select convert(date,'7/30/2016',101)
or if you just want a string with that format:select convert(varchar,convert(date,'7/30/2016',101),101)Actually, I want my string "7/30/2016" to convert to 30/7/2016
Any chance you can fix the architecture and store dates as the date datatype? It makes so many things a lot easier to choose the correct datatype. We don't always have that luxury though, we sometimes have to deal with the poor decisions of others. If you can store the data in the correct datatype this is super simple. You send the date to the front end and let the presentation layer deal with formatting where it best suited. Once you get this first converted to a date you can use CONVERT to make it output in a number of formats. https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017
That's what I want to do...
Getting closer. When I try the line below...it works on top 5000 files.
Case When [Date_from] <> '' Then Convert(varchar,Convert(date,[date_from], 101), 103) end as Date_from2But when i run it on the whole table, I get this :
Msg 241, Level 16, State 1, Line 2Conversion failed when converting date and/or time from character string.
Something like this might find the rows that are in error:SELECT *
FROM myTable
WHERE TRY_CONVERT(date, myVarcharDateCol,101) IS NULL
October 18, 2018 at 12:04 pm
GrassHopper - Thursday, October 18, 2018 11:37 AMThat's what I want to do...Getting closer. When I try the line below...it works on top 5000 files.
Case When [Date_from] <> '' Then Convert(varchar,Convert(date,[date_from], 101), 103) end as Date_from2But when i run it on the whole table, I get this :
Msg 241, Level 16, State 1, Line 2Conversion failed when converting date and/or time from character string.
Since this is a SQL 2016 board, I assume that you are using SQL 2016.
So try this, and when you are finished, then you can filter out all the records where ConvertedDate IS NULL to see why they are not convertingSELECT ConvertedDate = TRY_CONVERT(date, [date_from], 101)
October 18, 2018 at 1:45 pm
This is what worked for me below. There were some rows with the month names as values ie "FEBRUARY".
Try_Convert(varchar,Try_Convert(date,[date_From], 101), 103) as Date_To2
October 18, 2018 at 2:22 pm
GrassHopper - Thursday, October 18, 2018 1:45 PMThis is what worked for me below. There were some rows with the month names as values ie "FEBRUARY".
Try_Convert(varchar,Try_Convert(date,[date_From], 101), 103) as Date_To2
Glad that worked. One suggestion I would make is to never use varchar without specifying the length. When you don't it uses the default length. To make things more fun the default length changes on how it is being used. As a parameter or table column it will default to 1, inline like this it will default to 30. Don't risk it and be precise.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 18, 2018 at 2:28 pm
CAST is more flexible than specifying a specific format.
Thus, I'd suggest TRY_CAST ... AS date to perhaps allow more data to be automatically converted to a 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".
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply