Big Query - How to change date format

[davehutchinson]

Hi all.  Hopefully someone can help, I'm brand new to Big Query and these forums so hopefully I'm posting in the right place.  I've got 2 issues.  The first is how to convert the date to uk date format.  And the 2nd is the overall format of the script

I have a query in BigQuery but currently the date field "activity_create_dtm" is in the format of YY-MM-DDThh:mm:ss

I need to convert this to uk format.  I've worked out the following query:

FORMAT_DATE("%d-%m-%Y", DATE(activity_create_dtm), "2020-12-25") as UK_format;

This seems to work, however when I put that in the syntax error moved from that line, to my where lines at the end.

Here is my query:

select *,

CASE WHEN BB_LINE_TYPE LIKE '%FTTCFAST%' THEN 'FTTCFAST'

WHEN BB_LINE_TYPE LIKE '%FTTCWBC%' THEN 'FTTCWBC'

WHEN BB_LINE_TYPE LIKE '%FTTPWBC%' THEN 'FTTPWBC'

WHEN BB_LINE_TYPE LIKE '%SO_VDSL%' THEN 'SO_VDSL'

WHEN BB_LINE_TYPE LIKE '%SO_VSDL%' THEN 'SO_VDSL'

WHEN BB_LINE_TYPE LIKE '%GFAST%' THEN 'GFAST'

ELSE 'ADSL'

END as Access_tech,

--format_date(LEFT(activity_create_dtm,10),'YYYY-MM-DD', PARSE_DATE('DD/MM/YYYY', day)) as Create_date

FORMAT_DATE("%d-%m-%Y", DATE(activity_create_dtm), "2020-12-25") as UK_format;

FROM `bt-con-nrtprod-dp-data.cons_dp_nrtprod_data_product_ro.vw_dp_027_cons_nrtprod_engineering_appointments`

WHERE DATE(activity_create_dtm) >= '2024-12-10T00:00:00'

WHERE date(UK_format) >= '10-12-2024'

WHERE activity_source = 'Fault'

AND incident_product_line = 'Broadband'

The 2 lines in bold appear to be where the problem lies.  I only want to download data with a UK date of or after 10/12/24.  I've tried both lines, one at a time but neither work.

I'm getting the syntax error "WHERE not supported after FROM QUERY".  However I was told that you need to put your WHERE statement after the FROM in order to specify the criteria to download.  I've tried moving the statement to before the FROM statement and just get another error - "Unexpected keyword WHERE"

Can anyone spot where I'm going wrong?

[kolban · Accepted solution]

If I'm reading your query correctly, you have:

SELECT ... 
FROM <TABLE>
WHERE <EXPRESSION>
WHERE <EXPRESSION>
....

 That isn't valid SQL.  A SQL query can only have a single WHERE expression.  If you want the expression to have multiple predicates, you would conjoin them with "AND" ... for example

SELECT ....
FROM <table>
WHERE <EXPRESSION> AND <EXPRESSION>

This implies that the issue is not at all related to the data formatting but it is related to SQL grammer in general.

[davehutchinson]

That seems to have done the trick, thank you!