I need to devise a t-sql script to:
1.) loop through all user tables in my db,
2.) determine whether each table has one or more datetime fields, and
if it does,
3.) swap the year portion of each rows datetime with a different year
(e.g., '1-Jan-1996' would become '1-Jan-2018')
I know how to do '1', have an idea how to do '3' using the DatePart
and CAST functions, but haven't had much luck figuring out '2', and
*then iterating row by row through the table's datetime rows to swap
in the new years. To further automate this, I envisioned adding a
table to the db that defined the mapping for years, i.e.,...
tblBlindedYears
YearNow |BecomesYear
1996 2018
1997 2008
(With this, I would look to eliminate leap year issues by making sure
I don't swap a non-leap year with a leap-year).
Any help GREATLY appreciated.To help you with the issue 2):
USE Northwind
GO
SELECT c.TABLE_SCHEMA, c.TABLE_NAME, c.DATA_TYPE, COUNT(*) As 'NofCols'
FROM INFORMATION_SCHEMA.COLUMNS c INNER JOIN INFORMATION_SCHEMA.TABLES t
ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME
WHERE DATA_TYPE IN ('datetime','smaldatetime')
AND t.TABLE_TYPE='BASE TABLE'
GROUP BY c.TABLE_SCHEMA, c.TABLE_NAME, c.DATA_TYPE
ORDER BY c.TABLE_SCHEMA, c.TABLE_NAME, c.DATA_TYPE
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.
"JTWhaler" <jonahturnedwhaler@.yahoo.com> wrote in message
news:ed564348.0401021712.247c36da@.posting.google.com...
> I need to devise a t-sql script to:
> 1.) loop through all user tables in my db,
> 2.) determine whether each table has one or more datetime fields, and
> if it does,
> 3.) swap the year portion of each rows datetime with a different year
> (e.g., '1-Jan-1996' would become '1-Jan-2018')
> I know how to do '1', have an idea how to do '3' using the DatePart
> and CAST functions, but haven't had much luck figuring out '2', and
> *then iterating row by row through the table's datetime rows to swap
> in the new years. To further automate this, I envisioned adding a
> table to the db that defined the mapping for years, i.e.,...
> tblBlindedYears
> YearNow |BecomesYear
> 1996 2018
> 1997 2008
> (With this, I would look to eliminate leap year issues by making sure
> I don't swap a non-leap year with a leap-year).
> Any help GREATLY appreciated.|||This is very helpful, thank-you.
Now that I have this list of table names having counts of datetime
columns, any suggestions as to discovering *which* columns have the
datetime, and how I can begin printing these particular columns
values?
p.s. my results were:
dbo tbl10DayMeanAirTemp datetime 1
dbo tblHistoricalStationFlows datetime 1
dbo tblHistoricalStationWaterElevation datetime 1
dbo tblHistoricalStationWaterTemperatures datetime 1
dbo tblHydrologyModelWeeks datetime 2
dbo tblModelRuns datetime 5
dbo tblModelScenarios datetime 2
dbo tblXLFileLocations datetime 1
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in message news:<Ok3w2rd0DHA.1532@.TK2MSFTNGP10.phx.gbl>...
> To help you with the issue 2):
> USE Northwind
> GO
> SELECT c.TABLE_SCHEMA, c.TABLE_NAME, c.DATA_TYPE, COUNT(*) As 'NofCols'
> FROM INFORMATION_SCHEMA.COLUMNS c INNER JOIN INFORMATION_SCHEMA.TABLES t
> ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME
> WHERE DATA_TYPE IN ('datetime','smaldatetime')
> AND t.TABLE_TYPE='BASE TABLE'
> GROUP BY c.TABLE_SCHEMA, c.TABLE_NAME, c.DATA_TYPE
> ORDER BY c.TABLE_SCHEMA, c.TABLE_NAME, c.DATA_TYPE
> --
> Dejan Sarka, SQL Server MVP
> Please reply only to the newsgroups.
> "JTWhaler" <jonahturnedwhaler@.yahoo.com> wrote in message
> news:ed564348.0401021712.247c36da@.posting.google.com...
> > I need to devise a t-sql script to:
> >
> > 1.) loop through all user tables in my db,
> > 2.) determine whether each table has one or more datetime fields, and
> > if it does,
> > 3.) swap the year portion of each rows datetime with a different year
> > (e.g., '1-Jan-1996' would become '1-Jan-2018')
> >
> > I know how to do '1', have an idea how to do '3' using the DatePart
> > and CAST functions, but haven't had much luck figuring out '2', and
> > *then iterating row by row through the table's datetime rows to swap
> > in the new years. To further automate this, I envisioned adding a
> > table to the db that defined the mapping for years, i.e.,...
> >
> > tblBlindedYears
> > YearNow |BecomesYear
> > 1996 2018
> > 1997 2008
> >
> > (With this, I would look to eliminate leap year issues by making sure
> > I don't swap a non-leap year with a leap-year).
> >
> > Any help GREATLY appreciated.|||JTWhaler (jonahturnedwhaler@.yahoo.com) writes:
> This is very helpful, thank-you.
> Now that I have this list of table names having counts of datetime
> columns, any suggestions as to discovering *which* columns have the
> datetime, and how I can begin printing these particular columns
> values?
>
Here is a query that returns the column names as well:
select o.name, c.name
from sysobjects o
join syscolumns c on o.id = c.id
join systypes t on c.type = t.type
where t.name in ('datetime', 'smalldatetime')
order by o.name, c.name
(I assuming that everything is owned by dbo.)
To run some general code that goes through all columns, you would probably
have to use dynamic SQL to build statements on the fly.
If you are not acquainted with dynamic SQL, I have an article on my
web site, http://www.sommarskog.se/dynamic_sql.html. The article may
seem dauntingly long, but the article is trying to tell you that
you should use this feature with care. But this seems to be a good
occasion for using dynamic SQL.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns9466475B2A41Yazorman@.127.0.0.1>...
> JTWhaler (jonahturnedwhaler@.yahoo.com) writes:
> > This is very helpful, thank-you.
> >
> > Now that I have this list of table names having counts of datetime
> > columns, any suggestions as to discovering *which* columns have the
> > datetime, and how I can begin printing these particular columns
> > values?
> >
> Here is a query that returns the column names as well:
> select o.name, c.name
> from sysobjects o
> join syscolumns c on o.id = c.id
> join systypes t on c.type = t.type
> where t.name in ('datetime', 'smalldatetime')
> order by o.name, c.name
> (I assuming that everything is owned by dbo.)
> To run some general code that goes through all columns, you would probably
> have to use dynamic SQL to build statements on the fly.
> If you are not acquainted with dynamic SQL, I have an article on my
> web site, http://www.sommarskog.se/dynamic_sql.html. The article may
> seem dauntingly long, but the article is trying to tell you that
> you should use this feature with care. But this seems to be a good
> occasion for using dynamic SQL.
Thank-you very much. This is helpful, and I plan to read the dynamic SQL article.
