convert '1 - Apr' to datetime

Question

Post reply

convert '1 - Apr' to datetime

Snargables

SSCrazy Eights

Points: 8855
More actions
January 17, 2012 at 9:52 am

#255067

select replace('1 - Apr',' ','') dt
into #test
select dt --how do i convert this varchar to a datetime
from #test

Viewing 7 posts - 1 through 6 (of 6 total)

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

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 1

What year?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Snargables SSCrazy Eights Points: 8855 More actions · Answer 2

sorry. it's coming in a different column

select replace('1 - Apr',' ','') dt, 2010 [year]

into #test

select dt,year --how do i convert this varchar to a datetime

from #test

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 3

captcooldaddy (1/17/2012)
sorry. it's coming in a different column
select replace('1 - Apr',' ','') dt, 2010 [year]
into #test
select dt,year --how do i convert this varchar to a datetime
from #test

Can you set up a few rows of sample data exactly as it is in the source table, making sure the datatypes are the same? The link in my sig will show you how to do this.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 4

here's my first guess; there's a lot of flexibility in ISDATE and convert, you might get dates that are out of range.

;With mySampleData

AS

(

select '1 - Apr' As dt, 2010 AS [year] UNION ALL

SELECT 'august ',2011

)

select mySampleData.*,

CASE

WHEN ISDATE(dt + ' ' + CONVERT(varchar,[year])) = 1

THEN CONVERT(datetime,dt + ' ' + CONVERT(varchar,[year]))

ELSE NULL

END

FROM mySampleData

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Snargables SSCrazy Eights Points: 8855 More actions · Answer 5

thanks guys. I'll provide more infor in an hour or so. just got slammed.

Snargables SSCrazy Eights Points: 8855 More actions · Answer 6

actually never mind. That worked "SSCrazy Eights". Thank you guys...again...