Sql Date Format Conversion from Int(Yyyymmdd) Type to Date(Mm/Dd/Yyyy)

Get date from int (YYYYMMDD)

Select cast(cast(20160729 as varchar(10)) as date)

Returns

2016-07-29

Or

 Select cast(left(20160729,8) as date)

Date stored as INT but formated as YYYYMMDD trying to convert to date

You can use this:

SELECT CONVERT(date, CONVERT(varchar(8), 20201221), 112)

Explanation:

First you'll want to convert it to a string:

SELECT CONVERT(varchar(8), 20201221)

Then you want to convert that string, into a date. The date format you're storing it as, is 112 (see documentation here for other types)

So you can convert it by saying "convert this string into a date, here's a hint as to the format this date string is formatted so you know how to convert it into a date"

SELECT CONVERT(date, 'yyyyMMdd', 112)

---

EDIT:

In the comments you mentioned this was throwing errors for you, and it seems you have some inconsistent data. You wanted to be able to exclude conversions that would otherwise throw an error. This was my recommendation:

SELECT TRY_CONVERT(date, CONVERT(varchar(8), 20201221), 112) --Returns date
SELECT TRY_CONVERT(date, CONVERT(varchar(8), 20190229), 112) --Returns NULL, 2019 wasn't a leap year
SELECT TRY_CONVERT(date, CONVERT(varchar(8), 0), 112) --Returns NULL

TRY_CONVERT() is great for scenarios like this. You're able to convert all the values that are able to be converted, but you don't have a bunch of errors being thrown.

That said, it's good to see WHY those failed to convert. Perhaps you have some values that could be easily cleaned up first prior to being converted.

How to convert date in YYYYMMDD format (int) to datetime format in hive?

Figured it out. mm is minutes and MM is month.

It should be yyyyMMdd instead of yyyymmdd

How to convert a date format YYYY-MM-DD into integer YYYYMMDD in Presto/Hive?

If you just need to transform your date YYYY-MM-DD into an integer YYYYMMDD why don't you try to first remove all the occurrences of "-" from the string representation of your date before casting the result to int by using something like this?

cast(regexp_replace(str_column,'-','') as int)

Convert YYYYMMDD to MM/DD/YYYY in Snowflake

The issue with what you are doing is that you are assuming that Snowflake is converting your string of '20200730'::DATE to 2020-07-03. It's not. You need to specify your input format of a date. So, 2 options based on your question being a bit vague:

If you have a string in a table and you wish to transform that into a date and then present it back as a formatted string:

SELECT TO_VARCHAR(TO_DATE('20200730','YYYYMMDD'),'MM/DD/YYYY');
--07/30/2020

If the field in the table is already a date, then you just need to apply the TO_VARCHAR() piece directly against that field.

Unlike SQL Server, Snowflake stores date fields in the same format regardless of what you provide it. You need to use the TO_VARCHAR in order to format that date in a different way...or ALTER SESSION SET DATE_OUTPUT_FORMAT will also work.

Convert integer (YYYYMMDD) to date format (mm/dd/yyyy) in python

You can use datetime methods.

from datetime import datetime
a = '20160228'
date = datetime.strptime(a, '%Y%m%d').strftime('%m/%d/%Y')

Good Luck;

---

Related Topics