17.9.08

Convert Varchar to Datetime in sql server

Am having date, month and year as three columns and I need to append those data and convert into a date time. Let see how to achieve the same,
First,
We need to append those columns using Convert function in SQL Server.

I have tried to execute the below query,
select convert(datetime,
convert(varchar(10),convert(varchar(10),[month])+'/'+convert(varchar(10),[day])+'/'+convert(varchar(10),[year])))
from dbo.VenkatTable

I got this error,
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

Reason: Its because of the date format. I am trying to insert the date value into the month but, it wont accept beyond 12. Am i right :-)

I have changed the query. Now, its working fine.

select convert(datetime,convert(varchar(10),convert(varchar(10),[day])+'/'+convert(varchar(10),[month])+'/'+convert(varchar(10),[year])))from dbo.VenkatTable
Happy Learning!!!
Regards,

Venkatesan Prabu .J

at

1 comment:

  1. iDMarch 13, 2009 at 12:20 AM

    hi venkat,
    i faced the same error while saving the date in datetime column in sql server 2000..

    i would like to share my solution.
    in my dev. machine, the date insertion with mm/dd/yyyy worked very fine.. but in testing server and prodcution server.. it gave the error.. so i just converted it to the universal format accepted by sql server.. that is yyyy/MM/dd..

    i hope it helps...
    keep up the good work...
    regards
    dave,
    idu2u.blogspot.com

    ReplyDelete