Converting text to date issues

Question

Converting text to date issues

cephasknapp

SSC Enthusiast

Points: 111
More actions
June 12, 2012 at 4:12 am

#392496

I built into a view to extract from one field part of a text string and convert into date. It looks for the occurrence of a "/" and then returns the rest of the string, then converts the result. (I'm not much of a SQL programmer so it may look ungainly!):
CONVERT(datetime, SUBSTRING(dbo.F_TRACKING.TK_FLIGHT, CHARINDEX('/', dbo.F_TRACKING.TK_FLIGHT) + 1, LEN(dbo.F_TRACKING.TK_FLIGHT) + CHARINDEX('/', dbo.F_TRACKING.TK_FLIGHT)), 6) AS Expr10
As an example of what TK_FLIGHT contains, it looks like so: VBD55201/12MAY12
This is happily doing what I want it and I was then able to run a filter with dates, eg:
>= CONVERT(DATETIME, '2012-05-01 00:00:00', 102)
This also was happily doing what you would expect, however when I copy the code to another view with other filter parameters (although being exactly the same layout, only filters are different) it states:
"Conversion failed when converting datetime from character string"
???!!! Why would it work in one instance, then in another trip over??
Any help is hugely appreciated. If you need any further clarifications, then please let me know.
Regards
Peter

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Use the correct style when using convert. The first part of your code is nearly there, albeit missing the spaces between the date elements:

DECLARE @dbo_F_TRACKING_TK_FLIGHT VARCHAR(20)

SET @dbo_F_TRACKING_TK_FLIGHT = 'VBD55201/12MAY12'

-- Using RIGHT() is a little cleaner than SUBSTRING(). Then use STUFF to insert the spaces required by CONVERT style 6

SELECT CONVERT(datetime,

STUFF(STUFF(

RIGHT(@dbo_F_TRACKING_TK_FLIGHT, LEN(@dbo_F_TRACKING_TK_FLIGHT)-CHARINDEX('/', @dbo_F_TRACKING_TK_FLIGHT))

,3,0,' '),7,0,' ')

,6) AS Expr10

-- Turkey code (full of STUFFing)

SELECT CONVERT(datetime,

STUFF(STUFF(

STUFF(@dbo_F_TRACKING_TK_FLIGHT,1,CHARINDEX('/', @dbo_F_TRACKING_TK_FLIGHT),'')

,3,0,' '),7,0,' ')

,6) AS Expr10

Now lookup style 102 in BOL, under the CONVERT section, and see if the string '2012-05-01 00:00:00' exactly matches...I think style 120 is a closer fit.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

cephasknapp SSC Enthusiast Points: 111 More actions · Answer 2

Thanks Chris, there is one extra spanner in the works, the date in the text string if there is a single character day number, only returns 1 digit, not two.

so: 1st May 2012 is 1MAY12

NOT

01MAY12

This reminds me why I chose to have the number of the "/" character as a a variable in my calculations of how much of the string to cut out...

If I'm reading it right, by using "7" in the second STUFF we're assuming that the date/text is always in the DDMMMYY format. Unfortunately this data is not so consistent!

Or I might be reading your code wrong (as I say, I'm a bit new to this).

Thanks again

Peter

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

No problem. Left pad the text slice to 7 characters:

DECLARE @dbo_F_TRACKING_TK_FLIGHT VARCHAR(20)

SET @dbo_F_TRACKING_TK_FLIGHT = 'VBD55201/1MAY12'

-- Using RIGHT() is a little cleaner than SUBSTRING(). Then use STUFF to insert the spaces required by CONVERT style 6

SELECT CONVERT(datetime,

STUFF(STUFF(

RIGHT('0' + RIGHT(@dbo_F_TRACKING_TK_FLIGHT, LEN(@dbo_F_TRACKING_TK_FLIGHT)-CHARINDEX('/', @dbo_F_TRACKING_TK_FLIGHT)),7)

,3,0,' '),7,0,' ')

,6) AS Expr10

-- Turkey code (full of STUFFing)

SELECT CONVERT(datetime,

STUFF(STUFF(

RIGHT('0' + STUFF(@dbo_F_TRACKING_TK_FLIGHT,1,CHARINDEX('/', @dbo_F_TRACKING_TK_FLIGHT),''),7)

,3,0,' '),7,0,' ')

,6) AS Expr10

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

cephasknapp SSC Enthusiast Points: 111 More actions · Answer 4

A-ha! That's a nice solution.

Perfect... Thank you. Think I will explore STUFF a little more - hadn't seen or used it before (early days!). Quite useful for manipulating text streams...

Thanks again, much appreciated.

Peter