March 8, 2019 at 7:16 am
UCDA2019 - Thursday, March 7, 2019 6:55 AMroger.plowman - Thursday, March 7, 2019 6:36 AMConcerning the ambiguous dates, the dd/mm/yyyy format ends to be common in Europe and a few other places. Does your table have some kind of geographical identifier? Or can you relate to tables that do?Might give you a fighting chance of disambiguation.
Yeah the Table contains a 'SalesRegion' Column which uses numbers to identify a particular area
For example USA is 7
England is 6
UCDA2019 - Friday, March 8, 2019 4:52 AMYeah we seem to have a problem
eg.
11/12/2007
is appearing
as 12/11/2007any idea in which this could be fixed??
So, since you have an indicator to identify the region, then add code like this to my script.
Use the MS Docs site to look for patterns and slowly build up the query to cover your requirements.WHEN SalesRegion = 7 AND CreateDt LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 101)
WHEN SalesRegion = 7 AND CreateDt LIKE '[0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 110)
WHEN SalesRegion = 6 AND CreateDt LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 103)
WHEN SalesRegion = 6 AND CreateDt LIKE '[0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 105)
March 11, 2019 at 3:10 am
DesNorton - Friday, March 8, 2019 7:16 AMUCDA2019 - Thursday, March 7, 2019 6:55 AMroger.plowman - Thursday, March 7, 2019 6:36 AMConcerning the ambiguous dates, the dd/mm/yyyy format ends to be common in Europe and a few other places. Does your table have some kind of geographical identifier? Or can you relate to tables that do?Might give you a fighting chance of disambiguation.
Yeah the Table contains a 'SalesRegion' Column which uses numbers to identify a particular area
For example USA is 7
England is 6UCDA2019 - Friday, March 8, 2019 4:52 AMYeah we seem to have a problem
eg.
11/12/2007
is appearing
as 12/11/2007any idea in which this could be fixed??
So, since you have an indicator to identify the region, then add code like this to my script.
Use the MS Docs site to look for patterns and slowly build up the query to cover your requirements.WHEN SalesRegion = 7 AND CreateDt LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 101)
WHEN SalesRegion = 7 AND CreateDt LIKE '[0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 110)
WHEN SalesRegion = 6 AND CreateDt LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 103)
WHEN SalesRegion = 6 AND CreateDt LIKE '[0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 105)
There is no correlation between the date format and sales Region Des, However the dates appear in alphabetical order
I have included some data as example so the issue can be seen with dates such as 11/12/2014;
Old Date ------------------------------ New Date
11 December 2014------------------2014-12-11 00:00:00.000
11/12/2014----------------------------2014-11-12 00:00:00.000
11/12/2014----------------------------2014-11-12 00:00:00.000
11/12/2014----------------------------2014-11-12 00:00:00.000
11/12/2014--------------------------- 2014-11-12 00:00:00.000
12 December 2014-----------------2014-12-12 00:00:00.000
15/12/2014--------------------------- 2014-12-15 00:00:00.000
15/12/2014--------------------------- 2014-12-15 00:00:00.000
15 December 2014---------------- 2014-12-15 00:00:00.000
15/12/2014 -------------------------- 2014-12-15 00:00:00.000
15/12/2014--------------------------- 2014-12-15 00:00:00.000
15/12/2014 -------------------------- 2014-12-15 00:00:00.000
16 December 2014---------------- 2014-12-16 00:00:00.000
16 December 2014---------------- 2014-12-16 00:00:00.000
March 11, 2019 at 5:52 am
UCDA2019 - Monday, March 11, 2019 3:10 AMDesNorton - Friday, March 8, 2019 7:16 AMUCDA2019 - Thursday, March 7, 2019 6:55 AMroger.plowman - Thursday, March 7, 2019 6:36 AMConcerning the ambiguous dates, the dd/mm/yyyy format ends to be common in Europe and a few other places. Does your table have some kind of geographical identifier? Or can you relate to tables that do?Might give you a fighting chance of disambiguation.
Yeah the Table contains a 'SalesRegion' Column which uses numbers to identify a particular area
For example USA is 7
England is 6UCDA2019 - Friday, March 8, 2019 4:52 AMYeah we seem to have a problem
eg.
11/12/2007
is appearing
as 12/11/2007any idea in which this could be fixed??
So, since you have an indicator to identify the region, then add code like this to my script.
Use the MS Docs site to look for patterns and slowly build up the query to cover your requirements.WHEN SalesRegion = 7 AND CreateDt LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 101)
WHEN SalesRegion = 7 AND CreateDt LIKE '[0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 110)
WHEN SalesRegion = 6 AND CreateDt LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 103)
WHEN SalesRegion = 6 AND CreateDt LIKE '[0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 105)There is no correlation between the date format and sales Region Des, However the dates appear in alphabetical order
I have included some data as example so the issue can be seen with dates such as 11/12/2014;Old Date ------------------------------ New Date
11 December 2014------------------2014-12-11 00:00:00.000
11/12/2014----------------------------2014-11-12 00:00:00.000
11/12/2014----------------------------2014-11-12 00:00:00.000
11/12/2014----------------------------2014-11-12 00:00:00.000
11/12/2014--------------------------- 2014-11-12 00:00:00.000
12 December 2014-----------------2014-12-12 00:00:00.000
15/12/2014--------------------------- 2014-12-15 00:00:00.000
15/12/2014--------------------------- 2014-12-15 00:00:00.000
15 December 2014---------------- 2014-12-15 00:00:00.000
15/12/2014 -------------------------- 2014-12-15 00:00:00.000
15/12/2014--------------------------- 2014-12-15 00:00:00.000
15/12/2014 -------------------------- 2014-12-15 00:00:00.000
16 December 2014---------------- 2014-12-16 00:00:00.000
16 December 2014---------------- 2014-12-16 00:00:00.000
So, based on the samples, the dates are stored in the British/French format.
Then you can use CONVERT(SMALLDATETIME, CreateDt, 103)
March 11, 2019 at 6:16 am
I think due to the complications i am going to have to re-evaluate and use Microsoft Visual Studio with C#
March 11, 2019 at 8:33 am
UCDA2019 - Monday, March 11, 2019 6:16 AMI think due to the complications i am going to have to re-evaluate and use Microsoft Visual Studio
You are going to hit the same issues.
If you look at my script, it works systematically, and converts the "guaranteed" items first. You then get left with an ever decreasing set of data that you need to look at. When all is said and done, there is nothing that can automagically fix the headache that has been created by storing dates/times as strings. This problem is as old as programming is.
March 11, 2019 at 11:05 am
DesNorton - Monday, March 11, 2019 8:33 AMUCDA2019 - Monday, March 11, 2019 6:16 AMI think due to the complications i am going to have to re-evaluate and use Microsoft Visual StudioYou are going to hit the same issues.
If you look at my script, it works systematically, and converts the "guaranteed" items first. You then get left with an ever decreasing set of data that you need to look at. When all is said and done, there is nothing that can automagically fix the headache that has been created by storing dates/times as strings. This problem is as old as programming is.
Working through your method now Des up to about 60 options with roughly, working well completed around 80% of all fields
March 12, 2019 at 4:20 am
Hopefully this is the last time i have to write in this thread; out of 16998 fields i am down to just 3 following Des's method however theses dates are stored as follows
Friday, December 10,
Monday, December 13,
Monday, December 13,
This means having done some research
10 Decmeber 2010
13 December 2010
13 December 2010
With Des's method has anyone an idea how i get this into an appropriate date time
March 12, 2019 at 6:34 am
UCDA2019 - Tuesday, March 12, 2019 4:20 AMHopefully this is the last time i have to write in this thread; out of 16998 fields i am down to just 3 following Des's method however theses dates are stored as follows
Friday, December 10,
Monday, December 13,
Monday, December 13,This means having done some research
10 Decmeber 2010
13 December 2010
13 December 2010With Des's method has anyone an idea how i get this into an appropriate date time
This should do the trick. Hopefully this will be enough to convince whomever that you should always use the correct data types.
CASE WHEN CreateDt = 'Friday, December 10' THEN CONVERT(SMALLDATETIME, '2010-12-10', 121)
CASE WHEN CreateDt = 'Monday, December 13' THEN CONVERT(SMALLDATETIME, '2010-12-13', 121)
Viewing 8 posts - 31 through 37 (of 37 total)
You must be logged in to reply to this topic. Login to reply