May 7, 2015 at 7:07 am
I have a field called Period which stores a date range, i.e '01/01/15 - 20/01/15'
I can convert the first part into a start_date using:
substring(m.customer_order_no,1,2)+ '/' +substring(m.customer_order_no,4,2)+ '/' +substring(m.customer_order_no,7,2) as 'start_date'
However I want to put into date format as '01/01/2015', I am trying below but it keeps it as '01/01/15'
convert(varchar(10),substring(m.customer_order_no,1,2)+ '/' +substring(m.customer_order_no,4,2)+ '/' +substring(m.customer_order_no,7,2),103) as 'start_date'
Any ideas?
Thanks,
Gwyn
May 7, 2015 at 7:18 am
Period seems to have become customer_order_no in your code 🙂
Try casting to datetime or date first:
convert(varchar(10),cast(substring(m.customer_order_no,1,2)+ '/' +substring(m.customer_order_no,4,2)+ '/' +substring(m.customer_order_no,7,2) as date),103) as 'start_date'
Is this not easier though?:
CONVERT(VARCHAR(10), CAST(LEFT(m.customer_order_no, 8) AS DATE), 103)
May 7, 2015 at 7:19 am
Can you give example data for m.customer_order_no?
May 7, 2015 at 7:24 am
Gazareth, yes very observant, you are correct Period - I mean customer_order_no - '01/01/15 - 20/01/15' etc.
I am adapting old code, hence the numerous substrings - I don't know why left can't be used instead!!
Anyway, those queries didn't work - "Type DATE is not a defined system type."
Thanks,
Gwyn
May 7, 2015 at 7:35 am
gwyn.jones (5/7/2015)
I have a field called Period which stores a date range, i.e '01/01/15 - 20/01/15'I can convert the first part into a start_date using:
substring(m.customer_order_no,1,2)+ '/' +substring(m.customer_order_no,4,2)+ '/' +substring(m.customer_order_no,7,2) as 'start_date'
However I want to put into date format as '01/01/2015', I am trying below but it keeps it as '01/01/15'
convert(varchar(10),substring(m.customer_order_no,1,2)+ '/' +substring(m.customer_order_no,4,2)+ '/' +substring(m.customer_order_no,7,2),103) as 'start_date'
Any ideas?
Thanks,
Gwyn
Any chance you can fix the poorly designed tables instead? Ideally you would split this into two columns with the datetime datatype. Then you are no longer violating 1NF and you don't have to worry about "format" because the data would be in the proper datatype. This is not always possible of course when dealing with a legacy system as it sounds like this is.
_______________________________________________________________
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/
May 7, 2015 at 7:36 am
gwyn.jones (5/7/2015)
Gazareth, yes very observant, you are correct Period - I mean customer_order_no - '01/01/15 - 20/01/15' etc.I am adapting old code, hence the numerous substrings - I don't know why left can't be used instead!!
Anyway, those queries didn't work - "Type DATE is not a defined system type."
Thanks,
Gwyn
What version of sql server are you using? You posted in the 2008 forum so we assume you are using 2008 and DATE is a valid datatype in 2008.
_______________________________________________________________
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/
May 7, 2015 at 7:37 am
Sean Lange (5/7/2015)
gwyn.jones (5/7/2015)
Gazareth, yes very observant, you are correct Period - I mean customer_order_no - '01/01/15 - 20/01/15' etc.I am adapting old code, hence the numerous substrings - I don't know why left can't be used instead!!
Anyway, those queries didn't work - "Type DATE is not a defined system type."
Thanks,
Gwyn
What version of sql server are you using? You posted in the 2008 forum so we assume you are using 2008 and DATE is a valid datatype in 2008.
Beat me to it 🙂
DATETIME should be fine.
May 7, 2015 at 7:41 am
Maybe you are not able to cast as datetime because you have the dateformat different on your system then the format of the text version of your dates. This will work on any instance because we set the dateformat.
set dateformat dmy
declare @Something varchar(20) = '01/01/15 - 20/01/15'
select LEFT(@Something, 8)
, CAST(LEFT(@Something, 8) as datetime)
, RIGHT(@Something, 8)
, CAST(RIGHT(@Something, 8) as datetime)
_______________________________________________________________
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/
May 7, 2015 at 7:50 am
The problem with the database is that it is indeed a legacy system.
That field is actually a free entry field so a user can enter what they want.
The majority of the time it would be in the format as '01/01/15 - 20/01/15' but anything could be in there. I was going to extract the dates and put into 01/01/2015, and for anything else just bring across whatever the user has entered. I suppose this won't work as it cannot covert them into dates? Or I could just bring across '' if no valid date has been entered.
Yes, using 2008 version 10.50
I was hoping this would pretty straightforward, oh well, I can leave it as 01/01/15.
May 7, 2015 at 8:06 am
gwyn.jones (5/7/2015)
The problem with the database is that it is indeed a legacy system.That field is actually a free entry field so a user can enter what they want.
The majority of the time it would be in the format as '01/01/15 - 20/01/15' but anything could be in there. I was going to extract the dates and put into 01/01/2015, and for anything else just bring across whatever the user has entered. I suppose this won't work as it cannot covert them into dates? Or I could just bring across '' if no valid date has been entered.
Yes, using 2008 version 10.50
I was hoping this would pretty straightforward, oh well, I can leave it as 01/01/15.
Trying to convert free text into datetime is fraught with peril. The amount of validation and trial and error is staggering. If this is just to make the presentation a little nicer I would not bother.
_______________________________________________________________
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/
May 7, 2015 at 8:09 am
Yeah, I was probably thinking that this would be a quick and easy(ish) thing to do!
Thanks for all the help. I will leave as it is.
May 8, 2015 at 12:35 pm
Sean Lange (5/7/2015)
Trying to convert free text into datetime is fraught with peril. The amount of validation and trial and error is staggering. If this is just to make the presentation a little nicer I would not bother.
+1. Date widgets may be the best invention of the GUI era.
Don Simpson
May 8, 2015 at 3:19 pm
You could do a simple split of the string based on a "-", if needed, then use ISDATE() on one/both entries. That would tell you if SQL recognized it as a valid date or not. You might also look at the data and see if there are common exceptions you can handle accurately. But, doing anything beyond that could require very sophisticated logic, as already noted above.
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".
May 11, 2015 at 4:00 am
Thanks all for the help and advise.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply