I have the following SQL report running to give me all vulnerabilities that are Windows OS related and over 60 days since discovery but I want to format the date result for first_discovered so that it is just Month/Day/Year as opposed to the entire timestamp. I’ll be honest that I’m not the best at SQL and was wondering if anyone could provide some guidence on how to do this?
Currently the first_discovered date is exporting in this format “2025-03-12 05:30:11.741”
SQL Query is below.
WITH assets_grouped_by_site_and_vulnerability AS (
SELECT
site_id,
vulnerability_id,
first_discovered,
age_in_days,
array_to_string(
array_agg(
(ip_address) || (
CASE
WHEN host_name IS NULL THEN ‘’
ELSE ’ (’ || host_name || ‘)’
END
)
),
', ’
) AS affected_assets
FROM
fact_asset_vulnerability_age
JOIN dim_asset USING (asset_id)
JOIN dim_site_asset USING (asset_id)
GROUP BY
site_id,
vulnerability_id,
first_discovered,
age_in_days
)
SELECT
affected_assets AS “Affected Assets”,
dv.title AS “Vulnerability Title”,
dv.severity,
dv.cvss_score,
first_discovered AS “Vulnerability Discovery Date”
FROM
assets_grouped_by_site_and_vulnerability
JOIN dim_vulnerability dv USING (vulnerability_id)
JOIN dim_site ds USING (site_id)
WHERE
dv.cvss_score >= 7
AND dv.title ilike ‘%Microsoft Windows: cve%’
AND first_discovered < (NOW() - INTERVAL ‘60 days’)
ORDER BY
affected_assets,
first_discovered