February 3, 2020 at 8:51 pm
I have 50 tables that have all the columns as varchar. The columns have different data in each one such as dates, integers, currency, etc... Does anyone have a dynamic way to get the year/month/min/max of all columns that contain date information?
February 3, 2020 at 8:56 pm
What format are the dates in?
How would you know which columns to interrogate?
What do you mean by 'dynamic', exactly?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 3, 2020 at 9:25 pm
What format are the dates in? datetime (12/29/2019 00:00:00)
How would you know which columns to interrogate? I don't, I'm thinking any column that contains a "/" in it as a value.
What do you mean by 'dynamic', exactly? I wouldn't type in all the column names, so I guess it would do depend on what is saved in the column. This is why I guess it would have to be dynamic query.
February 3, 2020 at 9:42 pm
Here's the general structure. You can gen this out from the sys.columns table so you don't have to write out the code by hand.
SELECT
column_name,
COUNT(*) AS date_count, /*you need this to know what % of the rows had valid dates in that column*/
MIN(date) AS date_min,
MAX(date) AS date_max,
YEAR(date) AS date_year,
MONTH(date) AS date_month
FROM dbo.your_table_name
CROSS APPLY ( VALUES
('col1', TRY_CAST(col1 AS date)),
('col2', TRY_CAST(col2 AS date))
/* , ... */
) AS ca1(column_name, date)
WHERE ca1.date IS NOT NULL
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply