Need help on sql date query

Question

Post reply

Need help on sql date query

W.W.L

Valued Member

Points: 50
More actions
February 25, 2005 at 3:48 am

#60235

Hello *,
Of all datefields in my datebase, i need the number of values of each field that is lower than 1 jan 1990.
So a list :
Tablename + columnname + number of datevalue's <01-01-1990
For a list of all tables+datefields i used:
SELECT SYSOBJECTS.NAME ,SYSCOLUMNS.NAME
FROM SYSOBJECTS,SYSCOLUMNS
WHERE SYSCOLUMNS.XTYPE=61 AND SYSOBJECTS.ID=SYSCOLUMNS.ID AND SYSOBJECTS.TYPE= 'U'
ORDER BY SYSOBJECTS.NAME
But how to use this as an imput to get the number of datavalues?
Thanx for any help!

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

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Quick and dirty I would copy the results from

SELECT 'SELECT '+ SYSCOLUMNS.NAME +' FROM ' + SYSOBJECTS.NAME +' WHERE ' +SYSCOLUMNS.NAME +' <''19000101'''

FROM SYSOBJECTS,SYSCOLUMNS

WHERE SYSCOLUMNS.XTYPE=61 AND SYSOBJECTS.ID=SYSCOLUMNS.ID AND SYSOBJECTS.TYPE= 'U'

ORDER BY SYSOBJECTS.NAME

back into QA and execute it.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

W.W.L Valued Member Points: 50 More actions · Answer 2

Yes, but that would not return the number of value's, but the value's themselves. And i am suppost to make an sql script , and convert it to Oracle-syntax etc

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Sorry, for a COUNT(), what about:

SELECT 'SELECT COUNT(*) FROM ' + SYSOBJECTS.NAME +' WHERE ' +SYSCOLUMNS.NAME +' <''19000101'''

FROM SYSOBJECTS,SYSCOLUMNS

WHERE SYSCOLUMNS.XTYPE=61 AND SYSOBJECTS.ID=SYSCOLUMNS.ID AND SYSOBJECTS.TYPE= 'U'

ORDER BY SYSOBJECTS.NAME

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

W.W.L Valued Member Points: 50 More actions · Answer 4

Hi Frank,

Thanks for your reply. Yes, I can make a count(*) , but my problem is the combination of the 2 queries:

1) give the name of each datefield in the database and it's tablename

2) of the tablenames found in (1) , give the number ( so the count(*) ) of each table with a specific datevalue of the datefield ( e.g. >'01-01-1990' )

I can do each of these steps seperately, but to melt them into 1 sql string really puzzles me

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Hm, is this for administrational one-time purposes?

If yes, I would say, use a cursor, if no, what's wrong with separated statements?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

W.W.L Valued Member Points: 50 More actions · Answer 6

"what's wrong with separated statements?" Aha... well, if it would be for me, it's off course okay. But this script is for people who aren't DBA's, and therefor i want to make it as simple for them as possible. ( Plus off course i can learn a new trick on how to query the repository and use this output for input in a count(*) ( or an other statement ) )

wwL