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?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply