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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
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