March 19, 2015 at 5:16 am
I am using a custom sql query to import data into Tableau. Problem is I need to change the varchar column data in SQL currently returning 18/01/2014 08:35:13 as a format into the date format 05/21/2014 3:55:51 PM before I can use it.
Any assistance would be MUCH appreciated
March 19, 2015 at 5:32 am
cbosman0 (3/19/2015)
I am using a custom sql query to import data into Tableau. Problem is I need to change the varchar column data in SQL currently returning 18/01/2014 08:35:13 as a format into the date format 05/21/2014 3:55:51 PM before I can use it.Any assistance would be MUCH appreciated
Did you try CONVERT?? Note you may need to adjust the date config setting via SET DATEFORMAT to make sure the day/month/year are processed correctly.
Oh, and it is usually suboptimal or just plain wrong to store dates in character columns. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 19, 2015 at 6:11 am
I'm running the query against a replicated 3rd party database not setup by myself, which is proving to be very painful :ermm:
I may be doing something wrong (I'm helping out, havent used SQL in ages) but this is the query i have (attempting the convert) and the result is: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
select
convert(datetime,b.p12_value,101) as Call_Date
,a.user_name
,c.form_component_title
,c.form_component_id
,c.score
,c.max_possible_score
,b.form_key
from
[dbo].[Users] as A inner join [dbo].[Evals_on_form] as B on A.user_id = B.evaluatee_key
right join
(select
E.[eval_key]
,E.[score]
,E.max_possible_score
,F.[form_component_id]
,F.form_component_title
,E.creation_date
from
[dbo].[Evals_on_form_component] E inner join [dbo].[Form_components] F on E.form_component_key = F.form_component_key) C
on B.eval_key = C.eval_key
where b.form_key in (184)
March 19, 2015 at 6:31 am
CONVERT (datetime, value, 101)
is to convert U.S. date standsrt which is mm/dd/yyyy and in your first post you said you're trying to convert "18/01/2014 08:35:13" which is dd/mm/yyyy
try
convert(datetime,b.p12_value,103) as Call_Date
March 19, 2015 at 6:43 am
Hi,
Have amended and still getting the same error 🙁
March 19, 2015 at 6:47 am
Post some rows of [p12_value] column so we can see what we are dealing with.
March 19, 2015 at 7:00 am
There is likely some data in this field that cannot be converted to a date.
See what this returns
SELECT Field
FROM Table
WHERE ISDATE(Field) <> 1
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 19, 2015 at 7:01 am
21/01/2014 10:27:55
21/01/2014 16:20:59
21/01/2014 12:45:03
18/01/2014 08:35:13
22/01/2014 10:45:57
22/01/2014 10:57:46
22/01/2014 10:20:50
22/01/2014 17:40:24
22/01/2014 09:49:43
21/01/2014 10:49:36
17/01/2014 15:00:43
23/01/2014 11:42:55
23/01/2014 11:10:27
23/01/2014 09:23:22
23/01/2014 14:11:45
23/01/2014 11:53:05
23/01/2014 13:15:47
24/01/2014 10:23:58
25/01/2014 11:15:06
16/01/2014 10:00:25
24/01/2014 17:49:23
27/01/2014 10:58:12
27/01/2014 09:34:12
27/01/2014 08:10:15
26/01/2014 10:02:17
27/01/2014 12:56:28
24/01/2014 13:01:33
27/01/2014 12:38:22
24/01/2014 14:04:38
27/01/2014 14:58:59
27/01/2014 13:15:55
25/01/2014 16:14:18
27/01/2014 10:41:42
27/01/2014 16:21:11
27/01/2014 18:51:57
March 19, 2015 at 7:03 am
That is how the data starts, then it changes from the day month year to month day year in the beggining which is causing the issue...as below
3/16/2015 11:08:09 AM
3/16/2015 12:54:01 PM
3/16/2015 11:24:09 AM
3/17/2015 4:38:16 PM
3/16/2015 4:42:05 PM
3/16/2015 12:53:31 PM
3/16/2015 2:38:01 PM
3/16/2015 9:48:42 AM
3/17/2015 4:45:22 PM
3/16/2015 10:46:51 AM
3/18/2015 11:22:55 AM
3/16/2015 9:10:26 AM
3/16/2015 9:18:54 AM
3/17/2015 2:58:19 PM
3/16/2015 11:48:32 AM
3/16/2015 1:43:33 PM
3/16/2015 3:16:38 PM
3/18/2015 9:10:17 AM
3/18/2015 10:30:28 AM
3/16/2015 2:17:16 PM
3/17/2015 1:46:43 PM
3/17/2015 10:42:30 AM
3/17/2015 4:02:51 PM
3/17/2015 12:47:04 PM
3/17/2015 8:59:25 AM
March 19, 2015 at 7:07 am
Michael L John (3/19/2015)
There is likely some data in this field that cannot be converted to a date.See what this returns
SELECT Field
FROM Table
WHERE ISDATE(Field) <> 1
Hi,
With above it returns about half of the number of rows
March 19, 2015 at 7:09 am
cbosman0 (3/19/2015)
Michael L John (3/19/2015)
There is likely some data in this field that cannot be converted to a date.See what this returns
SELECT Field
FROM Table
WHERE ISDATE(Field) <> 1
Hi,
With above it returns about half of the number of rows
Yes because ISDATE depends on DATEFORMAT setting
changing from
SET DATEFORMAT mdy
to
SET DATEFORMAT dmy
or vice versa will give you other half
March 19, 2015 at 7:15 am
You can run your query 2 times and than merge the data in one resultset by inserting into a temp table etc.
SET DATEFORMAT mdy
select
convert(datetime,b.p12_value,101) as Call_Date
,a.user_name
,c.form_component_title
,c.form_component_id
,c.score
,c.max_possible_score
,b.form_key
from
[dbo].[Users] as A inner join [dbo].[Evals_on_form] as B on A.user_id = B.evaluatee_key
right join
(select
E.[eval_key]
,E.[score]
,E.max_possible_score
,F.[form_component_id]
,F.form_component_title
,E.creation_date
from
[dbo].[Evals_on_form_component] E inner join [dbo].[Form_components] F on E.form_component_key = F.form_component_key) C
on B.eval_key = C.eval_key
where b.form_key in (184) AND ISDATE(b.p12_value) = 1
-------------------------------------------------------------------------------------------------------------------
SET DATEFORMAT dmy
select
convert(datetime,b.p12_value,103) as Call_Date
,a.user_name
,c.form_component_title
,c.form_component_id
,c.score
,c.max_possible_score
,b.form_key
from
[dbo].[Users] as A inner join [dbo].[Evals_on_form] as B on A.user_id = B.evaluatee_key
right join
(select
E.[eval_key]
,E.[score]
,E.max_possible_score
,F.[form_component_id]
,F.form_component_title
,E.creation_date
from
[dbo].[Evals_on_form_component] E inner join [dbo].[Form_components] F on E.form_component_key = F.form_component_key) C
on B.eval_key = C.eval_key
where b.form_key in (184) AND ISDATE(b.p12_value) = 1
March 19, 2015 at 7:30 am
cbosman0 (3/19/2015)
Michael L John (3/19/2015)
There is likely some data in this field that cannot be converted to a date.See what this returns
SELECT Field
FROM Table
WHERE ISDATE(Field) <> 1
Hi,
With above it returns about half of the number of rows
The fields formatted like this are your issues:
21/01/2014 10:27:55
Something like this may work:
CASE WHEN ISDATE(field) = 0
THEN CONVERT(datetime, SUBSTRING(field, 4, 2) + '/' + LEFT(field, 2) + '/' + SUBSTRING(field, 7, LEN(field) - 6), 101)
ELSE CONVERT(datetime, Field)
END
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 19, 2015 at 7:32 am
CONVERT(datetime,[date],CASE WHEN [date] LIKE '%M' THEN 101 ELSE 103 END)
Far away is close at hand in the images of elsewhere.
Anon.
March 20, 2015 at 1:48 am
David Burrows (3/19/2015)
CONVERT(datetime,[date],CASE WHEN [date] LIKE '%M' THEN 101 ELSE 103 END)
You are legend, THANK YOU! Works :-D:-D:-D:-D
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply