May 12, 2017 at 2:44 am
I dont have a StringTime datatype. The reason its a varchar is because the data is imported into a temporary holding table from a .csv file which will subsequently be inserted into a live table where the column type is time7, so I want to do the conversion on the fly and import the data as time
May 12, 2017 at 2:45 am
solus - Friday, May 12, 2017 2:38 AMthese are all the string values in the table, none of them are out of time conversion range11:00:00.0000000
19:00:00.0000000
23:00:00.0000000
13:00:00.0000000
solus - Friday, May 12, 2017 1:37 AMIn my table I have a string column that contains a string representation of a time. The field could contains values such as
2300
1100
1900
1300so ive used some code to convert the string to a time, but its failing and i cant see why. This is the particular line in my statememnt thats failing
WHEN EndTime LIKE '[0-9][0-9][0-9][0-9]' THEN CONVERT(time, LEFT(EndTime,2) + ':' + RIGHT(EndTime,2) + ':00', 108)
none of the columns that contain 4 character times like 2300, 1300,1900, etc will convert, yet I know the method works because Ive used it on a diferent field that contains the same type of data (starttime)
ive checked the content length and its 4, but I just cant see what the problem is, can anyone see where im going wrong ?
So which is it?
And what's the data type?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 12, 2017 at 2:48 am
solus - Friday, May 12, 2017 2:44 AMI dont have a StringTime datatype. The reason its a varchar is because the data is imported into a temporary holding table from a .csv file which will subsequently be inserted into a live table where the column type is time7, so I want to do the conversion on the fly and import the data as time
Stringtime isn't a datatype, that's the name of the column as I don't have access to your data, so i had to create test data to test my answer. Perhaps written a different way might help you more.TRY_CAST(STUFF([YourColumnName],3,0,':') AS time) AS [NewColumnName]
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 12, 2017 at 3:01 am
the try-cast method worked, although it seemed to stop any folllowing commands from executing (I had multiple options in the case statement)
So I made it the last option in the case statement. There must be some spurious data in there somewhere but I just cant see it
May 12, 2017 at 3:06 am
solus - Friday, May 12, 2017 3:01 AMthe try-cast method worked, although it seemed to stop any folllowing commands from executing (I had multiple options in the case statement)
So I made it the last option in the case statement. There must be some spurious data in there somewhere but I just cant see it
You shouldn't need to CASE any more. If you simply want a column of times then you only need to SQL I gave you for that column (the TRY_CAST will happily handle "bad" values like "abcd" and return NULL). The CASE can be completely stripped out, unless you're doing some other kind of transformation on the data we don't know about.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 12, 2017 at 3:29 am
As Thom said, no need for a case statement, try_cast and try_convert simply return null if the value cannot be converted
😎
Here is a try_convert exampleUSE TEEST;
GO
SET NOCOUNT ON;
CREATE TABLE #Sample (StringTime char(40));
GO
INSERT INTO #Sample
VALUES
('2300'),
('1100'),
('1900'),
('1300'),
('not a time'),
('some other value'),
('135764');
GO
SELECT
X.StringTime
,TRY_CONVERT(TIME(0), STUFF(X.StringTime,3,0,':'),114) AS TimeValue
FROM #Sample X
WHERE TRY_CONVERT(TIME(0), STUFF(X.StringTime,3,0,':'),114) IS NOT NULL;
GO
DROP TABLE #Sample;
GO
Output
StringTime TimeValue
----------- ----------------
2300 23:00:00
1100 11:00:00
1900 19:00:00
1300 13:00:00
May 12, 2017 at 7:49 am
the problem is , this data is being inserted into a master table and it doesnt allow null date fields (the source is imported from a csv which should also never have null values)
I managed to get it to work eventually (marked as the answer) but I have a new problem with a different data file, but again its date related. My source data has string representations of dates, but they are in mixed format
01/09/2001 00:00
01-OCT-2014 00:00
01-MAY-2015 00:00
18/10/2016 00:00
so ive used
CONVERT(datetime, START_DATE) AS convertedSTART_DATE
and also tried
CAST(CONTRACT_DATE AS datetime) AS convertedCONTRACT_DATE
but its still falling over, how can I handle mixed formats like this ?
May 12, 2017 at 7:57 am
Firstly, let's find out what dates your SQL server doesn't like.
Could you give us some samples for the results that are returned for this query?SELECT START_DATE
FROM [Yourtable]
WHERE TRY_CAST(START_DATE AS date) IS NULL;
SELECT CONTRACT_DATE
FROM [Yourtable]
WHERE TRY_CAST(CONTRACT_DATE AS date) IS NULL;
Also, what is the language setting you have for your Login(s). If you had the date 01/02/17, are you expecting to enter 1st February 2017, or 2nd January 2012? Could some people have entered it the other way round (this will be a problem if so).
Ideally, however, you should be asking for your source data to be provided in an expected format. Having different types of date formats can make things quite messy.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 12, 2017 at 8:06 am
Ive asked if its possible to have consistent date formats in the csv file, but its exported from an external system so Im not sure if its possible, just waiting to hear back. In answer to your question, this is the result of query 1
SELECT START_DATE
FROM TmpContract
WHERE TRY_CAST(START_DATE AS date) IS NULL
go
18/10/2016 00:00
and query2
SELECT CONTRACT_DATE
FROM TmpContract
WHERE TRY_CAST(CONTRACT_DATE AS date) IS NULL
go
30/03/2017 00:00
May 12, 2017 at 8:11 am
This converts fine on my machine:SELECT TRY_CAST('18/10/2016 00:00' AS date)
Does that I can assume that you would see "18/10/2016" as the 10th day of the 18th month? You didn't answer my questions above about your date formats and language.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 12, 2017 at 8:21 am
date format is dmy
language is english (united states) although it should really be english UK, can I change this ?
May 12, 2017 at 8:27 am
solus - Friday, May 12, 2017 8:21 AMdate format is dmy
language is english (united states) although it should really be english UK, can I change this ?
The bolded part will be why these dates are failing. As it would read 18/10/17 as the 10th day of the 18th month. This also, HOWEVER, would mean the date '02/05/2017' would be converted to 2017-02-05.
If you've been doing this process awhile, I would strongly recommend checking any prior data.
You can change your language with, however, I do urge caution if you've been using US English all this time, or if other logins on the same server aren't using British:ALTER LOGIN [YourLogin] WITH DEFAULT_LANGUAGE = British;
GOThom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 12, 2017 at 8:29 am
this converts as null on mine
SELECT TRY_CAST('18/10/2016 00:00' AS date)
May 12, 2017 at 8:31 am
this is only a development environment so its not an issue, but id like to change it for the entire server , not just login specific so that its the same for everyone, is this possible ?
May 12, 2017 at 8:34 am
Ive just executed these two commands and restarted the server, but its still showing language as English (United States)
SET LANGUAGE British
go
set DATEFORMAT dmy
go
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply