Post reply

Unable to convert date into smalldatetime

  • December 26, 2007 at 9:26 am

    #76855

    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?

  • December 27, 2007 at 7:21 am

    #762928

    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

  • December 27, 2007 at 7:41 am

    #762936

    Try this

    SELECT CAST(GETDATE() AS SMALLDATETIME)

    Prasad Bhogadi
    www.inforaise.com

  • December 27, 2007 at 11:57 am

    #763060

    hi Prasad,

    i have already tried this . but same result here.

    Thanks

  • December 27, 2007 at 12:10 pm

    #763063

    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.

  • July 16, 2009 at 9:02 pm

    #1025977

    Try this

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

  • July 17, 2009 at 8:16 am

    #1026199

    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 6 (of 6 total)

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