Conver to Date

Question

Post reply

Conver to Date

Minnesota - Viking

Hall of Fame

Points: 3469
More actions
September 24, 2010 at 1:18 pm

#238777

I have a million rows ina table one of the columns is varchar(6) with dates in it like
200612
200705
200711
I want them to be converted to date format like YYYY/MM/DD
so that i can calculate the difference between dates on this coulmn and another column which is
varchar12 and has data like
10/1/2008
09/1/2010
10/1/2008.
So to convert my first column i tried
SELECT O_POOLSTART,CONVERT(datetime, O_POOLSTART,101) AS [MM/DD/YY] FROM FINC_AT_TRANS2
--But this gives me the following
200711 2020-07-11 00:00:00.000
200711 2020-07-11 00:00:00.000
200711 2020-07-11 00:00:00.000
200711 2020-07-11 00:00:00.000
200711 2020-07-11 00:00:00.000
200711 2020-07-11 00:00:00.000
If you notice instead of picking up year as 2007 it is picking it up as 2020
So how do i specify that my i/p format is YYYYMM and i need the o/p as MM/DD/YYYY
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]

Viewing 11 posts - 1 through 10 (of 10 total)

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

Nevyn SSChampion Points: 14210 More actions · Answer 1

Its not a date if there's no day. Try appending '01' onto the field then converting.

Minnesota - Viking Hall of Fame Points: 3469 More actions · Answer 2

u mean to say that if my data is like

20071101

it get converted perfectly ?

will i be still getting MM/DD/YYYY with my above mentioned convert statement ?

Thanks

[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]

Nevyn SSChampion Points: 14210 More actions · Answer 3

A mini example I build. I think this does what you want:

CREATE TABLE #test (myDate varchar(6))

INSERT INTO #test (myDate) values ('200612')

INSERT INTO #test (myDate) values ('200705')

INSERT INTO #test (myDate) values ('200711')

SELECT CONVERT(varchar(12),(CONVERT(datetime,myDate+'01')),101) FROM #test

DROP TABLE #test

This starts with a date represented as yyyymm in a varchar(6) and the query outputs a date that is the first of that month as a varchar(12) formatted mm/dd/yyyy.

Is that what you want?

Of course, if you want to compare it to a string, you never even have to convert it to a date at all

SELECT SUBSTRING(myDate,5,2)+'/01/'+SUBSTRING(myDate,1,4)FROM #test gives the same output.

Thats to get the format you want for display. If you want to use datediff or something to compare these to your other dates, then you can use:

SELECT CONVERT(datetime,myDate+'01') FROM #test

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

You can't just stick a few characters together and cast them as a date, there has to be some sort of delimiter (typically a '/'). If possible maybe you can consider changing the columns in both locations to date fields instead of varchars.

This should get you close.

create table #MyTable

(

datetype varchar(6)

)

insert #MyTable select '200612'

insert #MyTable select '200705'

insert #MyTable select '200711'

select * from #MyTable

select cast(RIGHT(datetype, 2) + '/01/' + LEFT(datetype, 4) as date) from #MyTable

drop table #MyTable

_______________________________________________________________

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/

Minnesota - Viking Hall of Fame Points: 3469 More actions · Answer 5

Thank You but the conversion failed. The error was

Conversion failed when converting datetime from character string.

Then i realised that some of my cells had invalid date formats in them like empty cells, some have the text written "no_date" etc

Is there something i can do to skip all the errors and write only valid values.

In my clase out of 100k rows 98k rows are being converted and then conversion failure error is being thrown.

What can i do to write NULL in result for all those invalid dates (2000).

[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]

Nevyn SSChampion Points: 14210 More actions · Answer 6

You could do a case statement that defaults them to a specific date if there isnt date information in the field ...

Minnesota - Viking Hall of Fame Points: 3469 More actions · Answer 7

Yes, but what would be the condition in case ?

with what condition can i filter all invalid input formats ?

Can i say if the i/p is number and of length 8 convert it

else default it to NULL

If this looks correct can you please put in into TSQL

Thanks

[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

something this should get you in the ballpark...

create table #MyTable

(

datetype varchar(6)

)

insert #MyTable select '200612'

insert #MyTable select '200705'

insert #MyTable select '200711'

insert #MyTable select 'nodate'

select * from #MyTable

where ISNUMERIC(datetype) = 1

select case when ISNUMERIC(datetype) = 1 then cast(RIGHT(datetype, 2) + '/01/' + LEFT(datetype, 4) as datetime) else null end

from #MyTable

drop table #MyTable

_______________________________________________________________

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/

LutzM SSC Guru Points: 107049 More actions · Answer 9

SeanLange (9/24/2010)
You can't just stick a few characters together and cast them as a date, there has to be some sort of delimiter (typically a '/'). If possible maybe you can consider changing the columns in both locations to date fields instead of varchars.
This should get you close.
...

Yes, We Can!!

It's actually one of the methods that'll work regardless of the DATEFORMAT setting.

Taking your very fine sample data from the previous post with two minor changes:

create table #MyTable

(

datetype varchar(6)

)

insert #MyTable select '200612'

insert #MyTable select '200705'

insert #MyTable select '200711'

--insert #MyTable select '200799' -- new row

insert #MyTable select 'nodate'

select * from #MyTable

where ISNUMERIC(datetype) = 1

SET DATEFORMAT ydm

select case when ISNUMERIC(datetype) = 1 then cast(RIGHT(datetype, 2) + '/01/' + LEFT(datetype, 4) as datetime) else null end

from #MyTable

-- versus

SELECT CASE WHEN ISDATE(datetype+'01') = 1 THEN CAST(datetype+'01' AS DATETIME) ELSE NULL END FROM #MyTable

drop table #MyTable

As you can see, the "delimiter solution" will return wrong results whereas the YYYYMMDD formatted solution will return the correct results, regardless of the DATEFORMAT setting.

Also, please note that I changed the verification method from ISNUMERIC to ISDATE.

You might want to try it with te value '200799' to see the difference 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Minnesota - Viking Hall of Fame Points: 3469 More actions · Answer 10

Thanks for the very informative feedback.

I thank all the commenters.

Love this forum. 🙂

[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]