--ISDATE() uses LANGUAGE and DATEFORMAT options to know that something is a date.
SET LANGUAGE english
SELECT ISDATE('2011 jan'), ISDATE('2011 gen') -- yes, no
--Language implicitly sets DATEFORMAT. "EXEC sp_helplanguage" and see "dateformat" column.
DBCC USEROPTIONS -- current settings
--ISDATE works ok only if you have separators between day, month and year, like hyphen, dot, slash etc:
SET DATEFORMAT dmy
select isdate('31012011') -- no
select isdate('31-01-2011') -- yes
select isdate('31 01 2011') -- no
select isdate('31.01.2011') -- yes
select isdate('31/01/2011') -- yes
SET DATEFORMAT mdy
select isdate('31012011') -- no
select isdate('31-01-2011') -- no
select isdate('31 01 2011') -- no
select isdate('31.01.2011') -- no
select isdate('31/01/2011') -- no
this works fine when date format in mm/dd/yyyy ,but when value is dd/mm/yyyy it generates this error messsage
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.
how can i leave the value of date format in dd/mm/yyyy and update mm/dd/yyyy olny?
thanks
I think this is due to a data problem. Then try
update Applicant_WorkExperience
set fromdate=convert(varchar,cast(fromdate as datetime), 103)
where isdate(cast(fromdate as datetime))=1;
go
Many Thanks & Best Regards,
HuaMin Chen
Marked as answer by Chen Yu - MSFT on Feb 26, 2013 02:04 AM
wmec
Contributor
6223 Points
3221 Posts
Re: ISDATE with dd/MM/yyyy doent work
Jan 31, 2013 09:07 AM|LINK
Try
SELECT ISDATE(CONVERT(datetime,'20/5/2009',103))
go
HuaMin Chen
NiravVyas
Participant
848 Points
200 Posts
Re: ISDATE with dd/MM/yyyy doent work
Jan 31, 2013 09:12 AM|LINK
Hi,
--ISDATE() uses LANGUAGE and DATEFORMAT options to know that something is a date.
SET LANGUAGE english
SELECT ISDATE('2011 jan'), ISDATE('2011 gen') -- yes, no
--Language implicitly sets DATEFORMAT. "EXEC sp_helplanguage" and see "dateformat" column.
DBCC USEROPTIONS -- current settings
--ISDATE works ok only if you have separators between day, month and year, like hyphen, dot, slash etc:
SET DATEFORMAT dmy
select isdate('31012011') -- no
select isdate('31-01-2011') -- yes
select isdate('31 01 2011') -- no
select isdate('31.01.2011') -- yes
select isdate('31/01/2011') -- yes
SET DATEFORMAT mdy
select isdate('31012011') -- no
select isdate('31-01-2011') -- no
select isdate('31 01 2011') -- no
select isdate('31.01.2011') -- no
select isdate('31/01/2011') -- no
Thanks,
Nirav..HTH
Thanks
Nirav
hady_matrix
Member
242 Points
252 Posts
Re: ISDATE with dd/MM/yyyy doent work
Jan 31, 2013 01:39 PM|LINK
thanks .it works.
how can i change date format from 5/20/2009 to 20/5/2009?
i tried this but not work
could you help me?thanks
wmec
Contributor
6223 Points
3221 Posts
Re: ISDATE with dd/MM/yyyy doent work
Feb 01, 2013 12:39 AM|LINK
Try
SELECT CONVERT(varchar,cast('5/20/2009' as datetime),103)
go
HuaMin Chen
hady_matrix
Member
242 Points
252 Posts
Re: ISDATE with dd/MM/yyyy doent work
Feb 03, 2013 07:01 AM|LINK
It doesnt work .the result is 20/05/2009.
any help please
wmec
Contributor
6223 Points
3221 Posts
Re: ISDATE with dd/MM/yyyy doent work
Feb 03, 2013 08:02 AM|LINK
Hi,
I do get this
20/05/2009
by
DECLARE @todate varchar(10)
SET @todate = CONVERT(VARCHAR(10), cast('5/20/2009' as datetime), 103)
Print @todate
HuaMin Chen
hady_matrix
Member
242 Points
252 Posts
Re: ISDATE with dd/MM/yyyy doent work
Feb 03, 2013 09:07 AM|LINK
thanks so much this works fine.
could you help me to update my table applying your query?
i used this,but there is error in script
this is to update all column values as replace all format from mm/dd/yyyy to dd/mm/yyyy
thanks
wmec
Contributor
6223 Points
3221 Posts
Re: ISDATE with dd/MM/yyyy doent work
Feb 03, 2013 09:33 AM|LINK
Try
HuaMin Chen
hady_matrix
Member
242 Points
252 Posts
Re: ISDATE with dd/MM/yyyy doent work
Feb 03, 2013 10:09 AM|LINK
i am so sorry.
this works fine when date format in mm/dd/yyyy ,but when value is dd/mm/yyyy it generates this error messsage
how can i leave the value of date format in dd/mm/yyyy and update mm/dd/yyyy olny?
thanks
wmec
Contributor
6223 Points
3221 Posts
Re: ISDATE with dd/MM/yyyy doent work
Feb 03, 2013 10:17 AM|LINK
I think this is due to a data problem. Then try
update Applicant_WorkExperience
set fromdate=convert(varchar,cast(fromdate as datetime), 103)
where isdate(cast(fromdate as datetime))=1;
go
HuaMin Chen