From DATETIME to SMALLDATETIME

• ChrisLee

  SSC-Addicted

  Points: 483

  More actions

  October 15, 2003 at 9:49 am

  #83330

  Hi,

  I have query:

  select case

  when

  CAST(DATEADD(....) AS SMALLDATETIME)

  ..........

  It does not always work. Sometimes, I got following error:

  "The conversion from datetime data type to smalldatetime data type resulted in a smalldatetime overflow error."

  Is there a better way to convert? Thanks.

  Chris

• paul_richardson

  SSCarpal Tunnel

  Points: 4594

  More actions

  October 15, 2003 at 10:08 am

  #478324

  hi,

  the range for smalldatetime is January 1, 1900 through June 6, 2079. if your dates are out of this range then this may be what is causing this error.

  HTH

  Paul

• ChrisLee

  SSC-Addicted

  Points: 483

  More actions

  October 15, 2003 at 10:34 am

  #478326

  Thanks Paul.

  I tried to convert Julian date (non-standard, JDE OneWorld format) to SQL smalldatetime. The conversion formular required to use DATEADD function which change Julian date CHAR(6) to DATETIME format. Say, if there is a problem with data itself. Is there safe guard I could use to prevent the error?

  Chris

• Johan Bijnens

  SSC Guru

  Points: 135374

  More actions

  October 16, 2003 at 12:54 am

  #478390

  check the ISDATE function in BOL

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me