英文:

Add random date to safe.parse_datetime when there is an absence of date

问题

I can safely parse_datetime using "%Y%m" to get the following:

unique_id	date_of_birth
1234	1976-01-01T00:00:00
5678	1987-12-01T:00:00:00

I'm happy with that, including the time, but I don't want the 1st as the date; I want the 15th for all of them. I can't quite remember how to do this. So my desired output would be:

unique_id	date_of_birth
1234	1976-01-15T00:00:00
5678	1987-12-15T:00:00:00

英文:

What it looks like:

unique_id	date_of_birth
1234	197601
5678	198712

So only year and month are present, e.g 1976 and 01 in the example above.

I can safe.parse_datetime with "%Y%m" to get the below:

unique_id	date_of_birth
1234	1976-01-01T00:00:00
5678	1987-12-01T:00:00:00

I'm happy with that including the time, but, I don't want the 1st as the date, I want the 15th for all of them. I can't quite work/out remember how to do this. So my desired output would be:

unique_id	date_of_birth
1234	1976-01-15T00:00:00
5678	1987-12-15T:00:00:00

答案1

得分: 1

只需尝试以下操作。

```sql
parse_datetime('%Y%m%d', date_of_birth || 15)

例如，

with sample_table as (
  select 1234 unique_id, '197601' date_of_birth union all
  select 5678, '198712'
)
select parse_datetime('%Y%m%d', date_of_birth || 15) from sample_table;

--查询结果
+---------------------+
|         f0_         |
+---------------------+
| 1976-01-15T00:00:00 |
| 1987-12-15T00:00:00 |
+---------------------+

<details>
<summary>英文:</summary>

Simply, you can try below.

```sql
parse_datetime(&#39;%Y%m%d&#39;, date_of_birth || 15)

For example,

with sample_table as (
  select 1234 unique_id, '197601' date_of_birth union all
  select 5678, '198712'
)
select parse_datetime('%Y%m%d', date_of_birth || 15) from sample_table;

--Query results
+---------------------+
|         f0_         |
+---------------------+
| 1976-01-15T00:00:00 |
| 1987-12-15T00:00:00 |
+---------------------+