converting varchar to datetime????

Question

Post reply

converting varchar to datetime????

grkanth81

Ten Centuries

Points: 1194
More actions
December 17, 2007 at 2:30 pm

#201743

i have a column named datecolumn varchar (5). it contains data something like this:
datecolumn
26NOV
09DEC
18NOV
22DEC
now i want to make that data as this format :
26/11/
09/12/
18/11/
22/12/
i am trying to do in this way---
select substring(datecolumn,1,2)+ '/'+replace(datecolumn,substring (datecolumn,3,3),month(datecolumn))+'/' from temp_table
i am getting this error--
Syntax error converting datetime from character string.

Viewing 13 posts - 1 through 12 (of 12 total)

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

>>month(datecolumn)

The MONTH() function only takes true datetime types.

A string value in DDMMM format won't implicitly convert to a datetime.

Since it appears as if year is irrelevant in your case, simply append any arbitrary year to the value in the table, so that the value will convert to a date that can be passed tp MONTH()

select

substring(datecolumn,1,2)+ '/' +

replace(datecolumn,substring (datecolumn,3,3),month(datecolumn+'2007')) +'/' from temp_table

grkanth81 Ten Centuries Points: 1194 More actions · Answer 2

thnx for ur reply...

i tried to run the query in the way u have suggested

select

substring(datecolumn,1,2)+ '/' +

replace(datecolumn,substring (datecolumn,3,3),month(datecolumn+'2007')) +'/' from temp_table

but i am getting the result in this way....

26/2611/

09/0912/

18/1811/

so on.............but i want like this 26/11/

09/12/

18/11/

Bryan Mischler SSC-Addicted Points: 422 More actions · Answer 3

Try this...

select

substring(datecolumn,1,2)+ '/' +

cast(month(substring (datecolumn,3,3)+'2007')as char(2)) +'/'

grkanth81 Ten Centuries Points: 1194 More actions · Answer 4

grkanth81

Ten Centuries

Points: 1194

December 17, 2007 at 7:38 pm

#760884

yep i got it....

thnx for ur response bryan.........

grkanth81 Ten Centuries Points: 1194 More actions · Answer 5

Hi Bryan,

thr is a small probs i am facing when running the query which u have sended...

there r some null values in the datecolumn...and they r coming in the result set in this way....

/1 /

i.e. it is showing the month of jan...

any possible solution to solve this probs?????

Bryan Mischler SSC-Addicted Points: 422 More actions · Answer 6

Bryan Mischler

SSC-Addicted

Points: 422

December 18, 2007 at 7:59 am

#761033

do you want nulls to remain nulls?

grkanth81 Ten Centuries Points: 1194 More actions · Answer 7

yes bryan..

for example if i am having something likt this :

datecolumn

26NOV

09DEC

18NOV

22DEC

then i shud get output like this:

26/11/

09/12/

18/11/

22/12/

the query which u have sended me....when i run it i am getting in this way:

26/11/

/ 1 /

09/12/

/1 /

18/11/

22/12/

and also for feb,mar,apr...i am getting as 2,3,4.....can i get them as 02,03,04????

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 8

grkanth81 (12/17/2007)
i have a column named datecolumn varchar (5). it contains data something like this:
datecolumn
26NOV
09DEC
18NOV
22DEC
now i want to make that data as this format :
26/11/
09/12/
18/11/
22/12/
i am trying to do in this way---
select substring(datecolumn,1,2)+ '/'+replace(datecolumn,substring (datecolumn,3,3),month(datecolumn))+'/' from temp_table
i am getting this error--
Syntax error converting datetime from character string.

1 Always use proper DATETIME datatype to store dates

2 It is your front end application that should do the formation

In case if you have no option, try

declare @date varchar(10)

set @date='26NOV'

select convert(varchar(6),cast(@date+'2007' as datetime),103)

Madhivanan

Failing to plan is Planning to fail

grkanth81 Ten Centuries Points: 1194 More actions · Answer 9

Thanx Madhvinan...for ur response

when i run the query which u have sended with my actual data...its working i.e i am getting the output correctly...but i am getting the following message also in the messages box...

''The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.''

does this have any effect????

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 10

grkanth81 (12/28/2007)
Thanx Madhvinan...for ur response
when i run the query which u have sended with my actual data...its working i.e i am getting the output correctly...but i am getting the following message also in the messages box...
''The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.''
does this have any effect????

Does your formatted dates are in proper format?

Check

Select .....

where isdate(your_col+'2007')=1

Madhivanan

Failing to plan is Planning to fail

grkanth81 Ten Centuries Points: 1194 More actions · Answer 11

Thanx Madhavinan...i got it....

but thr is a small probs still...

when i run tht query...i am getting data for only oct,nov and dec months...for the rest of the months...the rows r getting eliminated....

any suggestions plz???

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 12

grkanth81 (12/28/2007)
Thanx Madhavinan...i got it....
but thr is a small probs still...
when i run tht query...i am getting data for only oct,nov and dec months...for the rest of the months...the rows r getting eliminated....
any suggestions plz???

Are you sure?

select * from

(

select '12dec' as varchar_date union all

select '12jan' as varchar_date union all

select '12feb' as varchar_date union all

select '12mar' as varchar_date union all

select '12apr' as varchar_date

) as t

where isdate(varchar_date+'2007')=1

Madhivanan

Failing to plan is Planning to fail