SQL Server Performance

Datetime Vs Small datetime

Discussion in 'T-SQL Performance Tuning for Developers' started by sqldba_ind, Oct 4, 2007.

  1. sqldba_ind New Member

    Hi all
    I have table (column) with Small datetime as a datatype,I am trying to insert date time into Table it is giving Overfolow error
    can some body help me how to convert Datetime to small datetime beofre insert
    select convert(smalldatetime,getdate())
  2. Madhivanan Moderator

    What is wrong with this?
    declare @d smalldatetime
    set @d=getdate()
    select @d
  3. Adriaan New Member

    What is the result you see for SELECT GETDATE(), in the same connection?
    In any case, you don't need to convert GETDATE() to smalldatetime. Are there any other columns where the overflow could be occurring? It is already a little unusual to use smalldatetime, so perhaps other 'small' data types were chosen where 'big' ones are needed.
  4. Adriaan New Member

    If you have a statement that spells out the date, then make sure that the statement includes single quotes around the date.
    For instance, SELECT CAST(10-1-2007 AS smalldatetime)
    will cause an arithmetic overflow, since 10-1-2007 calculates to -1998, and smalldatetime only accepts positive integers. So SELECT CAST(2007-10-1 AS smalldatetime) does not run into error, but returns July 20, 1905.

    SELECT CAST('2007-10-01' AS DATETIME) would work fine.
  5. lcerni New Member

    I don't know if this information will help... I was having an issue moving data from one table where the column was datetime to another table where the column was smalldatetime. My problem was bad dates. We had dates greater than June 6, 2079.
    "Values for datetime data types earlier than January 1, 1753 are not permitted in SQL Server. SQL Server rejects all values that do not fall within the range from 1753 to 9999.
    Another SQL Server data type, smalldatetime, stores dates and times of day with less precision than datetime datatype. Valid date range for this data type is from January 1, 1900 through June 6, 2079."

Share This Page