Unable to convert date into smalldatetime

  • amit-470969

    SSC-Addicted

    Points: 437

    hi,

    i am using sql server 2005 and not able to convert date in to smalldatetime.

    query:

    select convert(smalldatetime,getdate())

    Error:

    Msg 298, Level 16, State 1, Line 1

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

    why?

  • Key DBA

    SSCertifiable

    Points: 6029

    amit,

    Your data in the DATETIME format may be out of the boundries of SMALLDATETIME.

    DATETIME

    January 1, 1753 through December 31, 9999

    SMALLDATETIME

    January 1, 1900 through June 6, 2079

    Microsoft SQL Server Books online Topic: datetime and smalldate

    Happy T-SQLing

    "Key"
    MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

  • Prasad Bhogadi

    SSCrazy Eights

    Points: 9235

    Try this

    SELECT CAST(GETDATE() AS SMALLDATETIME)

    Prasad Bhogadi
    www.inforaise.com

  • amit-470969

    SSC-Addicted

    Points: 437

    hi Prasad,

    i have already tried this . but same result here.

    Thanks

  • amit-470969

    SSC-Addicted

    Points: 437

    Hi Damon,

    thanks to reply.

    i think that there is not a data range problem , because

    here i m using getdate() for date.

    actually when i try following query

    >> Select Getdate()

    i got result.

    but when i try to convert or cast getdate into smalldatetime , so i m facing problem.

    >> select cast (getdate() as smalldatetime)

    i got error.

    i think that its a sqlserver 2005 installation problem. because when i try same query on another sqlserver 2005 ,its working.

  • dreamslogic

    Ten Centuries

    Points: 1131

    Try this

    SELECT CAST (floor (cast(getdate () AS SmallDatetime )) AS

  • Rudyx - the Doctor

    SSC-Forever

    Points: 43696

    both work for me:

    select cast (getdate() as smalldatetime)

    select convert(smalldatetime,getdate())

    select @@version

    results:

    -----------------------

    2009-07-17 09:16:00

    (1 row(s) affected)

    -----------------------

    2009-07-17 09:16:00

    (1 row(s) affected)

    ------------------------------------------------

    Microsoft SQL Server 2005 - 9.00.4207.00 (X64)

    (1 row(s) affected)

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply