Finding a table with date appended to it and check for the range of that date

Question

Finding a table with date appended to it and check for the range of that date

prasadau2006

SSC-Addicted

Points: 408
More actions
September 5, 2012 at 1:06 am

#393102

Hi All,
I need to create a process that looks for a table in database with date(YYYYMM) appeneded to it where MM can be any month of current quarter and check if there exists a table and dump that data into a table. I was able to get to the point of getting the last day and first day of current quarter to check for the range. But the problem is iam not sure how to search for a table with date appended for this specific range. Please help me out.
Thanks in advance

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

louigopal Mr or Mrs. 500 Points: 575 More actions · Answer 1

Hi Prasad,

In order to search for the table we can use sys.tables as below, also inorder to convert date to month you can use datepart function.

Hope this helps.

select * from sys.tables where name like '%YYYYMM%'

Thanks.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

Will your table suffix represent just year and month only YYYYMM or day as well YYYYMMDD?

What criteria do you want to apply? Month number and year? Date range? Anything else?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

prasadau2006 SSC-Addicted Points: 408 More actions · Answer 3

Eugene Elutin (9/5/2012)
Will your table suffix represent just year and month only YYYYMM or day as well YYYYMMDD?
What criteria do you want to apply? Month number and year? Date range? Anything else?

It just uses YYYYMM not date....no other criteria...i just have to check with this date falls under current quarter...the only problem is it is aappended to a table/tables and have to pull all thosee tables out.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 4

That will return all tables with suffix YYYYMM where YYYYMM represent every month of the current calendar quarter.

SELECT t.name

FROM sys.tables t

JOIN (SELECT '%' + CAST(QFM + m AS VARCHAR) AS QM

FROM ( SELECT CAST(YEAR(GETDATE()) AS VARCHAR(4))

+ RIGHT('0' + CAST(DATEPART(QUARTER,GETDATE()) * 3 AS VARCHAR(2)),2) AS QFM ) q

CROSS JOIN (SELECT 0 m UNION ALL SELECT 1 m UNION ALL SELECT 2 m) m

) qm3

ON t.name LIKE qm3.QM

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 5

Another way to get the table names...

SELECT st.Name

FROM sys.tables st

WHERE RIGHT(st.Name,6) IN

(

SELECT CONVERT(CHAR(6), DATEADD(mm,t.N,DATEADD(qq,DATEDIFF(qq,0,GETDATE()),0)), 112)

FROM (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2) t (N)

)

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 6

CELKO (9/9/2012)
>> I need to create a process that looks for a table in database with date(YYYYMM) appended to it where MM can be any month of current quarter and check if there exists a table and dump that data into a table. <<
This makes no sense. Rows are appended to a tables, not dates. Why are you PHYSICALLY moving data from table to table? SQL programmers use VIEWs.

He's not talking about appending rows to a table, Joe. He's talking about finding tables that have table names that are essentially identical with the only difference being that the names of the tables have had a year and month notation appended to them. This type of thing happens a lot when receiving data from 3rd party sources. If you don't have the Enterprise Edition of SQL Server, the tables are typically allowed to persist as they are named as a form of "poor man's partitioning" and are frequently assembled as a complete "entity" by using partitioned views.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)