char to date in sql server

Hi all

Im using sql server-2000 and storing date in varchar data type in dd/mm/yyyy format. how can i convert back to date in dd/mm/yyyy format

Thanks

Quote:

---

Originally Posted by searchajay

Hi all

Im using sql server-2000 and storing date in varchar data type in dd/mm/yyyy format. how can i convert back to date in dd/mm/yyyy format

Thanks

---

You must have a really good reason to store a date in VARCHAR format...

CONVERT function will convert most data formats from one type to another, and there are many date formats supported. Checks SQL Books Online for details...

Hi

Its giving me error when i execute following query

select convert(datetime,bookdate) from vwVatReturn
or
select convert(smalldatetime,bookdate) from vwVatReturn



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

I just did this in query analyzer

Code:

---

Declare @DateString varchar(10)
Declare @DateVbl datetime
Set @DateString='12/31/2005'
Print 'String='+@DateString
Set @DateVbl=Convert(Datetime,@DateString)
Print @DateVbl
Set @DateVbl=Cast(@DateString as datetime)
Print @DateVbl

---

And got this in the results panel...

Code:

---

String=12/31/2005
Dec 31 2005 12:00AM
Dec 31 2005 12:00AM

---

The dates show as Dec 31 2005 because that's the way the PRINT T-SQL command works - but they are in the datetime variable properly...

Hi szlamany

This works fine
Declare @DateString varchar(10)
Declare @DateVbl datetime
Set @DateString='04/19/2004'
Print 'String='+@DateString
Set @DateVbl=Convert(Datetime,@DateString)
Print @DateVbl
Set @DateVbl=Cast(@DateString as datetime)
Print @DateVbl

BUT THIS WILL GENERATE ERROR


Declare @DateVbl datetime
Set @DateString='19/04/2004'
Print 'String='+@DateString
Set @DateVbl=Convert(Datetime,@DateString)
Print @DateVbl
Set @DateVbl=Cast(@DateString as datetime)
Print @DateVbl

I mean to say if you use mm/dd/yyy format it works fine, but if you use dd/mm/yyyy than it will generate error


Thanks

That is because (like most/all DBMS's) SQL Server works internally in american date format, and as far as I am aware there is no way to switch it to UK format :(

Hi

Thanks, so i will change the format accordingly

Thanks