char to date in sql server

[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

[szlamany]

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...

[searchajay]

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.

[szlamany]

I just did this in query analyzer

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...

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...

[searchajay]

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

[si_the_geek]

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

[searchajay]

Hi

Thanks, so i will change the format accordingly

Thanks