Base_time in table mechanism is varchar type, want to into a format for the date '% % m Y' (201910) in this form, but the following statement after the transformation, base_time fields inside all become null null, I directly use DATE_FORMAT (base_time, '% % m Y') not

SELECT
DATE_FORMAT (STR_TO_DATE (base_time, '% % m Y'), '% % m Y) WD08, store_id WD27, sum (enter_store_incom) DL02, sum (enter_store_customer) DL01, sum (enter_store_frequency) DL03
The from t_report_customer_consume GROUP BY base_time, store_id

CodePudding user response:

 create table # c (sj datetime) 
Insert into c # select '2019-10-22' 

Select the left (the convert (nvarchar (20), sj, 120), 7) from # c 
Select substring (convert (nvarchar (20), sj, 120), 1, 4) + the substring (convert (nvarchar (20), sj, 120), 6, 2) date from # c 

If the string is direct interception, need not convert
As a beginner, I can only think of doing this

CodePudding user response:

 

SELECT substring (convert (varchar, getdate (), 112), 1, 6) as the date 


Date 
201910 

CodePudding user response:

refer to the second floor one tree forest _ response:

 

SELECT substring (convert (varchar, getdate (), 112), 1, 6) as the date 


Date 
201910 

 - exist to prevent short date 
Select substring (convert (varchar, cast (' 2019-9-20 10:20:11 'as a datetime), 112), 1, 6) as the date 
- normal date 
SELECT substring (convert (varchar, getdate (), 112), 1, 6) as the date