guddu567568



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'




Re: erorr

Manivannan.D.Sekaran


You can use the following statement..

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







Re: erorr

guddu

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.






Re: erorr

Manivannan.D.Sekaran

Ok.. What is your date format 12/05/06




Re: erorr

guddu

yes and that field is varchar



Re: erorr

Manivannan.D.Sekaran

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)






Re: erorr

guddu

i tried both queries getting error

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





Re: erorr

Manivannan.D.Sekaran

Is your AdditionalField3 has some invalid data




Re: erorr

guddu

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





Re: erorr

Manivannan.D.Sekaran

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)






Re: erorr

guddu

getting error

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





Re: erorr

Manivannan.D.Sekaran

Ooops Fixed , try now




Re: erorr

guddu

again error

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





Re: erorr

guddu

thanx a lot....gotted now..