Convert from DateTime to INT

by

EDIT: Casting to a float/int no longer works in recent versions of SQL Server. Use the following instead:

select datediff(day, '1899-12-30T00:00:00', my_date_field)
from mytable

Note the string date should be in an unambiguous date format so that it isn’t affected by your server’s regional settings.

In older versions of SQL Server, you can convert from a DateTime to an Integer by casting to a float, then to an int:

select cast(cast(my_date_field as float) as int)
from mytable

(NB: You can’t cast straight to an int, as MSSQL rounds the value up if you’re past mid day!)

If there’s an offset in your data, you can obviously add or subtract this from the result

You can convert in the other direction, by casting straight back:

select cast(my_integer_date as datetime)
from mytable

More Related Contents:

  1. Create a view with ORDER BY clause
  2. How to run the same query on all the databases on an instance?
  3. SQL Update with row_number()
  4. How to write UTF-8 characters using bulk insert in SQL Server?
  5. Is a single SQL Server statement atomic and consistent?
  6. Connecting to MS SQL Server with Windows Authentication using Python?
  7. SSIS Excel Import Forcing Incorrect Column Type
  8. determining the character set of a table / database?
  9. How do I pass value to a stored procedure parameter in OLE DB Source component?
  10. How to query for Xml values and attributes from table in SQL Server?
  11. A connection was successfully established with the server, but then an error occurred during the login process. (Error Number: 233)
  12. Should every User Table have a Clustered Index?
  13. How to load an XML file into a database using an SSIS package?
  14. Why does my ODBC connection fail when running an SSIS load in Visual Studio but not when running the same package using Execute Package Utility
  15. cross apply xml query performs exponentially worse as xml document grows
  16. Running an SSIS Package using dtexec
  17. SSIS reading LF as terminator when its set as CRLF
  18. Dynamically assign filename to excel connection string
  19. SQL Server Configuration Manager cannot be found
  20. Importing excel files having variable headers
  21. Temporarily disable all foreign key constraints
  22. Compute MD5 hash of a UTF8 string
  23. Sql Server replication requires the actual server name to make a connection to the server
  24. Convert a string with ‘YYYYMMDDHHMMSS’ format to datetime
  25. What are the differences between Merge Join and Lookup transformations in SSIS?
  26. How to execute SSIS package when a file is arrived at folder
  27. replace NULL values with latest non-NULL value in resultset series (SQL Server 2008 R2)
  28. To run a SSIS package outside of SQL Server Data Tools you must install Move File to Archive of Integration Services or higher
  29. SQL Server 2008 Spatial: find a point in polygon
  30. How to avoid SSIS FTP task from failing when there are no files to download?

Leave a Comment