date formatting

hi
below is my query AdditionalField3 is varchar datatype .and in this field my date is
storing like this 12/05/06..now i wanted to change format of this field AdditionalField3
like 2006-12-5

how to do this?
SELECT *
FROM Tbl_CMS_UploadDetails
WHERE AdditionalField3 = '2006-12-5'

You can use the following statement..

SELECT *
FROM Tbl_CMS_UploadDetails
WHERE Cast(AdditionalField3 as datetime) = Cast('2006-12-5' as datetime)

|||

i m getting below error

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

|||Ok.. What is your date format 12/05/06|||yes and that field is varchar|||

Ok..

If you use dd/mm/yy

SELECT *
FROM Tbl_CMS_UploadDetails
WHERE Convert(datetime, AdditionalField3, 103) = Cast('2006-12-5' as datetime)

if you use mm/dd/yy

SELECT *
FROM Tbl_CMS_UploadDetails
WHERE Convert(datetime, AdditionalField3, 101) = Cast('2006-12-5' as datetime)

|||

i tried both queries getting error

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

|||Is your AdditionalField3 has some invalid data?|||

yes this field also containing bank name ...we r using this field as common field so here for some cases we storing name and for other date thats why this field is varchar type ..for this purpose we r using format_id..

like this

for 83 we r storing date ..

SELECT *
FROM Tbl_CMS_UploadDetails
WHERE Convert(datetime, AdditionalField3, 101) = Cast('2006-12-5' as datetime) and format_id=83

|||

use the following query..

Code Snippet

--If you use dd/mm/yy

SET DATEFORMAT dmy

SELECT *

FROM Tbl_CMS_UploadDetails

WHERE Case When Isdate(AdditionalField3)=1 Then Convert(datetime, AdditionalField3, 103) Else NULL END = Cast('2006-12-5' as datetime)

--if you use mm/dd/yy

SET DATEFORMAT mdy

SELECT *

FROM Tbl_CMS_UploadDetails

WHERE Case When Isdate(AdditionalField3)=1 Then Convert(datetime, AdditionalField3, 101) Else NULL END = Cast('2006-12-5' as datetime)

|||

getting error

Server: Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'Then'.

|||Ooops Fixed , try now|||

again error

Server: Msg 241, Level 16, State 1, Line 3
Syntax error converting datetime from character string.

|||thanx a lot....gotted now..