0

I have data_date column (String data type) in table employee having value in YYYYMMDD format.

Please suggest solution to find first day of month based on data_date column.

for example :

data_Date - 20181217 (String Value)

Output - 20181201 (String Value)

A Saraf
  • 315
  • 4
  • 20

2 Answers2

0
SELECT DATEADD(month, DATEDIFF(month, 0,CONVERT(date, '20170825')), 0) AS StartOfMonth

Substitute your column for date string

RN92
  • 1,380
  • 1
  • 13
  • 32
  • Tried below query and getting error: SELECT DATEADD(month, DATEDIFF(month, 0,CONVERT(date, data_as_of_daily_date)), 0) AS StartOfMonthfrom table_name ////////////////////// AnalysisException: Syntax error in line 1: ...DATEDIFF(month, 0,CONVERT(date, data_as_of_daily_date)... ^ Encountered: DATE Expected: ALL, CASE, CAST, DEFAULT, DISTINCT, EXISTS, FALSE, IF, INTERVAL, NOT, NULL, REPLACE, TRUNCATE, TRUE, IDENTIFIER CAUSED BY: Exception: Syntax error – A Saraf Jan 11 '19 at 04:47
  • Can you please put your query? Does that column has 'NULL' values? – RN92 Jan 11 '19 at 04:49
  • Column has only tow values 20181217 and 20181119. I am running below query on Impala and It is throwing error. SELECT DATEADD(month, DATEDIFF(month, 0,CONVERT(DATE, data_as_of_daily_date)), 0) AS StartOfMonthfrom table_name – A Saraf Jan 11 '19 at 04:53
  • I am still facing the same issue. Do you have any other solution for this? – A Saraf Jan 11 '19 at 11:47
0

How about something like this?

select left(data_date, 6) || '01'
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I have used the below query to find first date of month and it is returning the result as "2018-12-01 00:00:00" Expected output - 20181201(String type) SELECT DATE_ADD(FROM_UNIXTIME(UNIX_TIMESTAMP(DATA_AS_OF_DAILY_DATE, 'yyyyMMdd')), 1 - DAY(FROM_UNIXTIME(UNIX_TIMESTAMP(DATA_AS_OF_DAILY_DATE, 'yyyyMMdd')))) AS firstdayofmonth FROM table – A Saraf Jan 11 '19 at 13:24