Convert INT to DATETIME (SQL)
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))
Convert an Int to a date field
Simple cast as date could work
Select cast(cast(20161011 as varchar(8)) as date)
Returns
2016-10-11
If your data is suspect, you could also use Try_Convert()
Select Try_Convert(date,cast(2610 as varchar(8)))
Returns
NULL
CONVERT Date INT to DATE in SQL
(Read below for my answer for if it's an int
column)
Assuming it's a textual string:
Assuming that datadate
is a string (character, text, etc) column and not a date
/datetime
/datetime2
/datetimeoffset
column, then use the CONVERT
function with style: 23
. The 23
value corresponds to ISO 8601 because the values are in yyyy-MM-dd
-order, even though they're missing dashes.
This page has a reference of style
numbers: https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15
SELECT
*
FROM
(
SELECT
myTable.*
CONVERT( date, datadate, 23 ) AS valueAsDate
FROM
myTable
) AS q
WHERE
q.valueAsDate = DATEADD( dd, -1, GETDATE() )
Assuming it's an actual int
column:
The quick-and-dirty way is to convert the int
to varchar
and then use the same code as above as if it were a textual field - but don't do this because it's slow:
SELECT
*
FROM
(
SELECT
myTable.*,
CONVERT( char(8), datadate ) AS valueAsChar,
CONVERT( date, CONVERT( char(8), datadate ), 23 ) AS valueAsDate
FROM
myTable
) AS q
WHERE
q.valueAsDate = DATEADD( dd, -1, GETDATE() )
Assuming it's an actual int
column (better answer):
We'll need to use DATEFROMPARTS
and extract each component using Base-10 arithmetic (fun)!
If we have an integer representing a formatted date (the horror) such as 20200531
then:
- We can get the day by performing
MOD 31
(e.g.19950707 MOD 31 == 7
) - We can get the month by first dividing by 100 to remove the day part, and then
MOD 12
: (e.g.20200531 / 100 == 202005
,202005 MOD 12 == 5
) - We can get the year by dividing by
10,000
, (e.g.20200531 / 10000 == 2020
).
Btw:
- SQL Server uses
%
for the Modulo operator instead ofMOD
. - Integer division causes truncation rather than producing decimal or floating-point values (e.g.
5 / 2 == 2
and not2.5
).
Like so:
SELECT
q2.*
FROM
(
SELECT
q.*,
DATEFROMPARTS( q.[Year], q.MonthOfYear, q.DayOfMonth ) AS valueAsDate
FROM
(
SELECT
myTable.*,
( datadate % 31 ) AS DayOfMonth,
( ( datadate / 100 ) % 12 ) AS MonthOfYear,
( datadate / 10000 ) AS [Year]
FROM
myTable
) AS q
) AS q2
WHERE
q2.valueAsDate = DATEADD( dd, -1, GETDATE() )
Obviously, having two nested subqueries is a pain to work with (SQL has terrible ergonomics, I don't understand how or why SQL doesn't allow expressions in a SELECT
clause to be used by other expressions in the same query - it's really bad ergonomics...) - but we can convert this to a scalar UDF (and SQL Server will inline scalar UDFs so there's no performance impact).
This function has a TRY/CATCH
block in it because of the possibility that you process an invalid value like 20209900
(which isn't a real date as there isn't a 99th month with a 0th day in 2020). In this event the function returns NULL
.
CREATE FUNCTION dbo.convertHorribleIntegerDate( @value int ) RETURNS date AS
BEGIN
DECLARE @dayOfMonth int = @value % 31;
DECLARE @monthOfYear int = ( @value / 100 ) % 100;
DECLARE @year int = @value / 10000;
BEGIN TRY
RETURN DATEFROMPARTS( @dayOfMonth, @monthOfYear, @year );
END TRY;
BEGIN CATCH
RETURN NULL;
END CATCH;
END
Which we can use in a query like so:
SELECT
myTable.*,
dbo.convertHorribleIntegerDate( datadate ) AS valueAsDate
FROM
myTable
As SELECT
cannot share expression results with other expressions in the same query, you'll still need to use an outer query to work with valueAsDate
(or repeat the dbo.convertHorribleIntegerDate
function call):
SELECT
*
FROM
(
SELECT
myTable.*,
dbo.convertHorribleIntegerDate( datadate ) AS valueAsDate
FROM
myTable
) AS q
WHERE
q.valueAsDate = DATEADD( dd, -1, GETDATE() )
How to convert int to date in SQL Server 2008
You can't convert an integer value straight to a date but you can first it to a datetime then to a date type
select cast(40835 as datetime)
and then convert to a date (SQL 2008)
select cast(cast(40835 as datetime) as date)
cheers
Convert integer value to DateTime in SQL Server 2012
Almost every time you see a date/time represented as an integer, that number represents the passage of time since a known epoch. This is the basis of Unix time which is, put simply, the number of seconds which have elapsed since 1st January 1970 00:00:00
Using this, we can check with some values you have provided
declare @dt DATETIME = '1970-01-01' -- epoch start
print dateadd(second,978307200,@dt ) -- Jan 1 2001 12:00AM
print dateadd(second,1472300673,@dt ) -- Aug 27 2016 12:24PM
Seems possible, but who knows?!
You can check every date in your table simply using
declare @dt DATETIME = '1970-01-01' -- epoch start
SELECT
nDateTime AS OriginalData,
DATEADD(second, nDateTime,@dt) AS ActualDateTime
FROM EventLog
SQL Convert Integer to DateTime
Jain, it looks like you are trying to adapt the code in the answer here to your own situation. What Andomar did there was to select the information from a subquery which contained only a single value, since he didn't have access to the original poster's source and tables. Doing it that way allows everyone to run and see it, whatever database schema they happen to have.
Adapting something like that to your own code would be very easy: just replace the alias.column
reference (here, it was sub.Dt
) with your own value, and put your table in the FROM clause instead of the subquery. Thus, if your table was named SomeTable
, and your column is named FetchTime
, the query becomes:
select
convert(datetime, substring(SomeTable.FetchTime,1,4) + '-' +
substring(SomeTable.FetchTime,5,2) + '-' +
substring(SomeTable.FetchTime,7,2) + ' ' +
substring(SomeTable.FetchTime,9,2) + ':' +
substring(SomeTable.FetchTime,11,2) + ':' +
substring(SomeTable.FetchTime,13,2))
from
SomeTable
You could theoretically have a problem if your FetchTime
field was stored as an integer: SUBSTRING() might not work on that data type. To work around that, you could CAST()
the value as varchar, like this:
select
convert(datetime, substring(CAST(SomeTable.FetchTime AS VARCHAR(20)),1,4) + '-' +
substring(CAST(SomeTable.FetchTime AS VARCHAR(20)),5,2) + '-' +
substring(CAST(SomeTable.FetchTime AS VARCHAR(20)),7,2) + ' ' +
substring(CAST(SomeTable.FetchTime AS VARCHAR(20)),9,2) + ':' +
substring(CAST(SomeTable.FetchTime AS VARCHAR(20)),11,2) + ':' +
substring(CAST(SomeTable.FetchTime AS VARCHAR(20)),13,2))
from
SomeTable
Or, if you are just after the single value, you could go back to the original query and simply write:
select
convert(datetime, substring(sub.Dt,1,4) + '-' +
substring(sub.Dt,5,2) + '-' +
substring(sub.Dt,7,2) + ' ' +
substring(sub.Dt,9,2) + ':' +
substring(sub.Dt,11,2) + ':' +
substring(sub.Dt,13,2))
from
(select CAST(FetchTime AS VARCHAR(20)) as Dt FROM SomeTable) sub
Related Topics
How to Delete a Fixed Number of Rows with Sorting in Postgresql
How to Find Rows in One Table That Have No Corresponding Row in Another Table
Custom Function with Check Constraint SQL Server 2008
Procedure or Function !!! Has Too Many Arguments Specified
How to Find Third or Nᵗʰ Maximum Salary from Salary Table
SQL Server Select Where Any Column Contains 'X'
Use a Union or a Join - What Is Faster
Error 1111 (Hy000): Invalid Use of Group Function
Sql: Order by Using a Substring Within a Specific Column... Possible
Foreign Key Creation Issue in Oracle
How to Store a List in a Db Column
How to Pass a List as a Parameter in a Stored Procedure
Split Words with a Capital Letter in SQL
Why Doesn't SQL Support "= Null" Instead of "Is Null"
Postgresql - SQL - Count of 'True' Values