Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL query

Question

I have a table with a column that stores the date and time  I need to write a query to get only the date from that column   SELECT CAST CONVERT VARCHAR  LoginTime  101  AS datetime  FROM AuditTrail    But  when I run the query I am getting this error      The conversion of a varchar data type to a datetime data type resulted in an out-of-range value    the data in the column is datetime ex  2012-06-18 12 08 04 000 so i need to extract the date only and remove the time note that the  Logintime  column is datatime format

User · Answer

hope this may help you   SELECT  CAST LoginTime AS DATE           FROM    AuditTrail    If you want to have some filters over this datetime or it s different parts  you can use built-in functions such as Year and Month

User · Answer

some problem  but I find the solution  this is          2  February Feb   28  29 in leap years    this is my code   public string GetCountArchiveByMonth int iii               iii  is number of months  use any number other than    2                 con Open                 SqlCommand cmd10   con CreateCommand                cmd10 CommandType   CommandType Text              cmd10 CommandText    select count id post  from posts where dateadded between CONVERT VARCHAR   start  103  and CONVERT VARCHAR   end  103                cmd10 Parameters AddWithValue   start        iii     01 2019                cmd10 Parameters AddWithValue   end        iii     30 2019                string result   cmd10 ExecuteScalar   ToString                 con Close                 return result              now for test   lbl1 Text   GetCountArchiveByMonth   7    ToString        here use any number other than    2             because of check   February   is maxed 28 days

User · Answer

I struggled with the same problem  I have stored dates in SQL Server with format  YYYY-MM-DD HH NN SS  for about 20 years  but today that was not able anymore from a C  solution using OleDbCommand and a UPDATE query  The solution to my problem was to remove the hyphen - in the format  so the resulting formatting is now  YYYYMMDD HH MM SS   I have no idea why my previous formatting not works anymore  but I suspect there is something to do with some Windows updates for ADO

User · Answer

as you can see on the answer to this question  Conversion of a varchar data type to a datetime data type resulted in an out-of-range value  -- set the dateformat for the current session set dateformat dmy  -- The conversion of a varchar data type  -- to a datetime data type resulted in an out-of-range value  select cast  2017-08-13 16 31 31   as datetime   -- get the current session date format select date format from sys dm exec sessions where session id     spid  -- set the dateformat for the current session set dateformat ymd  -- this should work select cast  2017-08-13 16 31 31   as datetime

User · Answer

Try ISDATE   function in SQL Server  If 1  select valid date  If 0 selects invalid dates   SELECT cast CONVERT varchar  LoginTime  101  as datetime    FROM AuditTrail  WHERE ISDATE LoginTime    1    Click here to view result   EDIT     As per your update i need to extract the date only and remove the time  then you could simply use the inner CONVERT  SELECT CONVERT VARCHAR  LoginTime  101  FROM AuditTrail    or  SELECT LEFT LoginTime 10  FROM AuditTrail   EDIT 2     The major reason for the error will be in your date in WHERE clause ie   SELECT cast CONVERT varchar  LoginTime  101  as datetime    FROM AuditTrail where CAST CONVERT VARCHAR  LoginTime  101  AS DATE   lt    CAST  06 18 2012  AS DATE    will be different from   SELECT cast CONVERT varchar  LoginTime  101  as datetime    FROM AuditTrail where CAST CONVERT VARCHAR  LoginTime  101  AS DATE   lt    CAST  18 06 2012  AS DATE    CONCLUSION  In EDIT 2 the first query tries to filter in mm dd yyyy format  while the second query tries to filter in dd mm yyyy format  Either of them will fail and throws error     The conversion of a varchar data type to a datetime data type resulted   in an out-of-range value    So please make sure to filter date either with mm dd yyyy or with dd mm yyyy format  whichever works in your db

[sql] Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL query

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to datetime