how to convert varchar into smalldatatime

From: new (anonymous_at_discussions.microsoft.com)
Date: 09/16/04

  • Next message: Hugo Kornelis: "Re: How to Convert Character String to Numeric- Thanks!" 
    Date: Thu, 16 Sep 2004 13:07:03 -0700

    I import text file into SQL table, one column
    name 'date', datatype is varchar and string in field
    like 'dd/mm/yy'. I want to convert varchar into
    smalldatetime and output is 101 'mm/dd/yyyy'. my query is:

    alter table name1 alter column date smalldatetime

    error massage:Server: Msg 296, Level 16, State 3, Line 1
    The conversion of char data type to smalldatetime data
    type resulted in an out-of-range smalldatetime value.
    The statement has been terminated.

    then I try to convert.

    alter table name1 add date1 smalldatetime
    update name1
    set date1=convert(varchar, date,101 )

    error message:Server: Msg 296, Level 16, State 3, Line 1
    The conversion of char data type to smalldatetime data
    type resulted in an out-of-range smalldatetime value.
    The statement has been terminated.

    Thanks reply

  • Next message: Hugo Kornelis: "Re: How to Convert Character String to Numeric- Thanks!"

    Relevant Pages

    • Re: Insert 1800 in SQL Server 2000
      ... "Junior" wrote in message ... char data type to smalldatetime date type resulted in an out of range ... you've gotten the main answer but you also need to format the date better, '18000101' is not a date, '1800-01-01' is. ...
      (microsoft.public.vb.general.discussion)
    • Re: VARCHAR to SMALLDATETIME
      ... >I have a field in a table which is a VARCHAR and holds dates as dd-mm-yyyy. ... >I have tried CONVERT(SMALLDATETIME, Date, 5) and CAST ... And if you really really really have to store a date in a character ... store it in an unambigous format. ...
      (microsoft.public.sqlserver.msde)
    • expects parameter ??? , which was not supplied."
      ... @PatientID VarChar, ... @PreparedDate smalldatetime, ... @BleedingIntercourse SmallInt, ... @cdContactName1 VarChar, ...
      (microsoft.public.dotnet.framework.adonet)
    • Re: expects parameter ??? , which was not supplied."
      ... > @PatientID VarChar, ... > @PreparedDate smalldatetime, ... > @BleedingIntercourse SmallInt, ... > @cdContactName1 VarChar, ...
      (microsoft.public.dotnet.framework.adonet)
    • Re: time & n date query
      ... WHERE DateEnd>= CAST, varChar, 112) AS SmallDateTime) ... Converting to varchar in the specified format, drops the hh:mm:ss, and then ... : DateEnd Desc" ...
      (microsoft.public.inetserver.asp.general)