[sql] Convert INT to DATETIME (SQL)

I am trying to convert a date to datetime but am getting errors. The datatype I'm converting from is (float,null) and I'd like to convert it to DATETIME.

The first line of this code works fine, but I get this error on the second line:

Arithmetic overflow error converting expression to data type datetime.

CAST(CAST( rnwl_efctv_dt AS INT) AS char(8)),
CAST(CAST( rnwl_efctv_dt AS INT) AS DATETIME),

This question is related to sql datetime casting integer

The answer is

you need to convert to char first because converting to int adds those days to 1900-01-01

select CONVERT (datetime,convert(char(8),rnwl_efctv_dt ))

here are some examples

select CONVERT (datetime,5)

1900-01-06 00:00:00.000

select CONVERT (datetime,20100101)

blows up, because you can't add 20100101 days to 1900-01-01..you go above the limit

convert to char first

declare @i int
select @i = 20100101
select CONVERT (datetime,convert(char(8),@i))

Try this:

select CONVERT(datetime, convert(varchar(10), 20120103))

Source: Stackoverflow.com

Examples related to sql

• Passing multiple values for same variable in stored procedure • SQL permissions for roles • Generic XSLT Search and Replace template • Access And/Or exclusions • Pyspark: Filter dataframe based on multiple conditions • Subtracting 1 day from a timestamp date • PYODBC--Data source name not found and no default driver specified • select rows in sql with latest date for each ID repeated multiple times • ALTER TABLE DROP COLUMN failed because one or more objects access this column • Create Local SQL Server database

Examples related to datetime

• Comparing two joda DateTime instances • How to format DateTime in Flutter , How to get current time in flutter? • How do I convert 2018-04-10T04:00:00.000Z string to DateTime? • How to get current local date and time in Kotlin • Converting unix time into date-time via excel • Convert python datetime to timestamp in milliseconds • SQL Server date format yyyymmdd • Laravel Carbon subtract days from current date • Check if date is a valid one • Why is ZoneOffset.UTC != ZoneId.of("UTC")?

Examples related to casting

• Subtracting 1 day from a timestamp date • Cast object to interface in TypeScript • TypeScript enum to object array • Casting a number to a string in TypeScript • Hive cast string to date dd-MM-yyyy • Casting int to bool in C/C++ • Swift double to string • No function matches the given name and argument types • C convert floating point to int • PostgreSQL : cast string to date DD/MM/YYYY

Examples related to integer

• Python: create dictionary using dict() with integer keys? • How to convert datetime to integer in python • Can someone explain how to append an element to an array in C programming? • How to get the Power of some Integer in Swift language? • python "TypeError: 'numpy.float64' object cannot be interpreted as an integer" • What's the difference between integer class and numeric class in R • PostgreSQL: ERROR: operator does not exist: integer = character varying • C++ - how to find the length of an integer • Converting binary to decimal integer output • Convert floats to ints in Pandas?