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://learn.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 a column of type integer to type datetime in python?
Just apply pd.to_datetime
directly to (the string conversion of) the column, no need to use string slicing here:
Freshman['ADMIT_DATE'] = pd.to_datetime(Freshman['ADMIT_DATE'].astype(str), format='%Y%m')
There is no requirement for there to be a delimiter between the digits:
>>> import pandas as pd
>>> df = pd.DataFrame({'ADMIT_DATE': [200110, 201604]})
>>> df['ADMIT_DATE'] = pd.to_datetime(df['ADMIT_DATE'].astype(str), format='%Y%m')
>>> df
ADMIT_DATE
0 2001-10-01
1 2016-04-01
>>> df.dtypes
ADMIT_DATE datetime64[ns]
dtype: object
Int to date in SQL
This works for me in SQL Server 2017:
select cast(cast(20190430 as nvarchar) as DATE) AS DateResult
Output:
DateResult
2019-04-30
Convert Integer field Date time to Date Time
You can use DATEADD
to convert UNIX timestamp to DATETIME
and FORMAT
function to format it:
SELECT FORMAT(DATEADD(SECOND, 1512543210, '19700101'), 'dd-MM-yyyy hh:mm:ss')
-- 06-12-2017 06:53:30
Having said that, Java has DateTimeFormatter class for formatting dates. And timestamps could be used to construct date objects directly.
How to convert integer into date object python?
I would suggest the following simple approach for conversion:
from datetime import datetime, timedelta
s = "20120213"
# you could also import date instead of datetime and use that.
date = datetime(year=int(s[0:4]), month=int(s[4:6]), day=int(s[6:8]))
For adding/subtracting an arbitary amount of days (seconds work too btw.), you could do the following:
date += timedelta(days=10)
date -= timedelta(days=5)
And convert back using:
s = date.strftime("%Y%m%d")
To convert the integer to a string safely, use:
s = "{0:-08d}".format(i)
This ensures that your string is eight charecters long and left-padded with zeroes, even if the year is smaller than 1000 (negative years could become funny though).
Further reference: datetime objects, timedelta objects
convert integer to date in python
You can represent dates with the .dt
method of the Series
Adapted from my similar answer https://stackoverflow.com/a/66038817/4541045
import pandas as pd
df = pd.DataFrame({"dates": [200101, 200202, 202010]})
# dates
# 0 200101
# 1 200202
# 2 202010
df.dates = pd.to_datetime(df.dates, format="%Y%m") # make a datetime
# dates
# 0 2001-01-01
# 1 2002-02-01
# 2 2020-10-01
df.dates.dt.strftime("%Y%m") # represent datetime Series
# 0 200101
# 1 200202
# 2 202010
# Name: dates, dtype: object
NOTE df.dates
is just referring to the column named dates
in this case
Related Topics
Login Failed. the Login Is from an Untrusted Domain and Cannot Be Used with Windows Authentication
Postgres Query of an Array Using Like
Global Variables in SQL Statement
Performance of String Comparison VS Int Join in SQL
Sql: Syntax Error with Intersect
Oracle 12C - Select String After Last Occurrence of a Character
SQL - Safely Downcast Bigint to Int
Oracle, Split a Time Duration Row by One Hour Period
Parse a Date from Unformatted Text in SQL
SQL Server Query Xml Attribute for an Element Value
How Does SQL Server Wildcard Character Range, Eg [A-D], Work with Case-Sensitive Collation
How to Make a JPA Query with Left Outer Join
SQL Server - Group Records by N Minutes Interval