ISDATE with dd/MM/yyyy doent work RSS

• • Reply
  

  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
  
  

  Many Thanks & Best Regards,
  HuaMin Chen
  
• • Reply
  

  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
  

  Hope this helps
  
  Thanks
  Nirav
• • Reply
  

  hady_matrix

  Member

  242 Points

  252 Posts

  Re: ISDATE with dd/MM/yyyy doent work

  Jan 31, 2013 01:39 PM|LINK

  wmec

  Try

  SELECT ISDATE(CONVERT(datetime,'20/5/2009',103))
  go

  thanks .it works.

  how can i change date format from 5/20/2009 to 20/5/2009?

  i tried this but not work

  DECLARE @todate varchar(10)
  SET @todate = CONVERT(VARCHAR(10),  '5/20/2009', 106)
  Print @todate
  
  result is :2/20/2011

  could you help me?thanks
  
  

  http://www.rasoulallah.net/index_english.asp
• • Reply
  

  wmec

  Contributor

  6223 Points

  3221 Posts

  Re: ISDATE with dd/MM/yyyy doent work

  Feb 01, 2013 12:39 AM|LINK

  hady_matrix

  thanks .it works.

  how can i change date format from 5/20/2009 to 20/5/2009?

  i tried this but not work

  DECLARE @todate varchar(10)
  SET @todate = CONVERT(VARCHAR(10),  '5/20/2009', 106)
  Print @todate
  
  result is :2/20/2011

  could you help me?thanks
  

  Try

  SELECT CONVERT(varchar,cast('5/20/2009' as datetime),103)
  go
  
  

  Many Thanks & Best Regards,
  HuaMin Chen
  
• • Reply
  

  hady_matrix

  Member

  242 Points

  252 Posts

  Re: ISDATE with dd/MM/yyyy doent work

  Feb 03, 2013 07:01 AM|LINK

  wmec

  SELECT CONVERT(varchar,cast('5/20/2009' as datetime),103)

  It doesnt work .the result is 20/05/2009.

  any help please

  http://www.rasoulallah.net/index_english.asp
• • Reply
  

  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
  
  

  Many Thanks & Best Regards,
  HuaMin Chen
  
• • Reply
  

  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

  update Applicant_WorkExperience set fromdate=cast(fromdate as datetime), 103)

  this is to update all column values as replace all format from mm/dd/yyyy to dd/mm/yyyy

  thanks
  
  

  http://www.rasoulallah.net/index_english.asp
• • Reply
  

  wmec

  Contributor

  6223 Points

  3221 Posts

  Re: ISDATE with dd/MM/yyyy doent work

  Feb 03, 2013 09:33 AM|LINK

  Try

  update Applicant_WorkExperience set fromdate=convert(varchar,cast(fromdate as datetime), 103);
  go

  Many Thanks & Best Regards,
  HuaMin Chen
  
  • Edited by hady_matrix on Feb 03, 2013 09:44 AM
  • Edited by hady_matrix on Feb 03, 2013 10:05 AM
  • Edited by hady_matrix on Feb 03, 2013 10:05 AM
  • Edited by hady_matrix on Feb 03, 2013 10:09 AM
• • Reply
  

  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

  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
  
  

  http://www.rasoulallah.net/index_english.asp
• • Reply
  

  wmec

  Contributor

  6223 Points

  3221 Posts

  Re: ISDATE with dd/MM/yyyy doent work

  Feb 03, 2013 10:17 AM|LINK

  hady_matrix

  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

  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