Convert INT to DATETIME (SQL)

by

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))

More Related Contents:

  1. How to return only the Date from a SQL Server DateTime datatype
  2. DateTime2 vs DateTime in SQL Server
  3. SQL query to select dates between two dates
  4. PostgreSQL: running count of rows for a query ‘by minute’
  5. How to combine date from one field with time from another field – MS SQL Server
  6. Get day of week in SQL Server 2005/2008
  7. SQL Server Convert Varchar to Datetime
  8. Time zone storage in data type “timestamp with time zone”
  9. How to determine the number of days in a month in SQL Server?
  10. Convert integer to hex and hex to integer
  11. MySQL’s now() +1 day
  12. Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL
  13. Calculate business hours between two dates
  14. SQL Server convert string to datetime
  15. Double colon `::` notation in SQL
  16. SQL error “ORA-01722: invalid number”
  17. A way to extract from a DateTime value data without seconds
  18. Discard millisecond part from timestamp
  19. How do I cast a string to integer and have 0 in case of error in the cast with PostgreSQL?
  20. Difference of two date time in sql server
  21. Custom Date/Time formatting in SQL Server
  22. How can I convert a Sql Server 2008 DateTimeOffset to a DateTime
  23. How to convert PostgreSQL 9.4’s jsonb type to float
  24. How to use Timestamp_to_scn and Scn_to_timestamp in Oracle?
  25. Convert String to Date in MS Access Query
  26. How to change string date to MySQL date format at time of import of CSV using MySQL’s LOAD DATA LOCAL INFILE
  27. Converting String List into Int List in SQL
  28. Why is 30 the default length for VARCHAR when using CAST?
  29. Truncate timestamp to arbitrary intervals
  30. MySQL: Typecasting NULL to 0

Leave a Comment