Without DateTime Column?

Question

Post reply

Without DateTime Column?

Charmer

SSChampion

Points: 14903
More actions
January 24, 2012 at 9:15 am

#391390

Hi Friends,
i have table which does not have any date time column, but my need is to get some records depending on the date&time...is it possible to get the records? or is there any other solution?
Please help me friends
Thanks,
Charmer

Viewing 10 posts - 1 through 9 (of 9 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Is the relevant date and time in another table, like an audit log or something?

If not, you can't get data out of a database that was never put into it.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

herladygeekedness SSCertifiable Points: 5878 More actions · Answer 2

Sounds like someone gave you lousy requirements!

In my opinion, if there is no column in which to check the criteria, you cannot use that criteria.

That said, not knowing where your table data comes from nor if you are populating it or if it's source data, is there a relationship to another table that does contain date/time data that you can use?

Charmer SSChampion Points: 14903 More actions · Answer 3

herladygeekedness (1/24/2012)
Sounds like someone gave you lousy requirements!
In my opinion, if there is no column in which to check the criteria, you cannot use that criteria.
That said, not knowing where your table data comes from nor if you are populating it or if it's source data, is there a relationship to another table that does contain date/time data that you can use?

yes...this requirement sucks me off...

Thanks,
Charmer

herladygeekedness SSCertifiable Points: 5878 More actions · Answer 4

Never good to go back and explain why something isn't possible, but, you might have to. I will hope for your sake that the info you seek is available, but they assumed you already knew where it is hiding!

Fear Naught SSC Veteran Points: 240 More actions · Answer 5

I always add columns to a table that records the date and time a record was created and by whom. I also have columns to show when records were last modified and by whom using defaults and triggers to populate them. Ideally I should have a full audit trail but my current project doesn't require that and as a newbie to SQL Server and self taught I haven't got to that lesson yet!! 🙂

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

Charmer (1/24/2012)
herladygeekedness (1/24/2012)
Sounds like someone gave you lousy requirements!
In my opinion, if there is no column in which to check the criteria, you cannot use that criteria.
That said, not knowing where your table data comes from nor if you are populating it or if it's source data, is there a relationship to another table that does contain date/time data that you can use?
yes...this requirement sucks me off...

This is easy. Find the hack that designed the table and have him/her explain it to management. 😛

--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)

Charmer SSChampion Points: 14903 More actions · Answer 7

Jeff Moden (2/19/2012)
Charmer (1/24/2012)
herladygeekedness (1/24/2012)
Sounds like someone gave you lousy requirements!
In my opinion, if there is no column in which to check the criteria, you cannot use that criteria.
That said, not knowing where your table data comes from nor if you are populating it or if it's source data, is there a relationship to another table that does contain date/time data that you can use?
yes...this requirement sucks me off...
This is easy. Find the hack that designed the table and have him/her explain it to management. 😛

That's what exactly i did...:-)

Thanks,
Charmer

Eric M Russell SSC Guru Points: 125562 More actions · Answer 8

Charmer (1/24/2012)
Hi Friends,
i have table which does not have any date time column, but my need is to get some records depending on the date&time...is it possible to get the records? or is there any other solution?
Please help me friends

Perhaps you can join to another table that has a date/time.

If this is a financial or security audit request, and your job depends on it, then perhaps the solution to your problem can start by querying the transaction log using the undocumented (but occasionally blogged about) fn_dblog function. You can try filtering on insert operations by object (table in your case) and [Begin Time] - [End Time]. If there is at least a sequential identifer column and you somehow determine the max / min value for a time frame, then you can use that to go back and query the table.

http://sqlskills.com/BLOGS/PAUL/post/Finding-out-who-dropped-a-table-using-the-transaction-log.aspx

This may end up being one of those situations where you go though the motions. step-by-step, and demonstrate to the requestor why it's not really possible and then hope they eventually come to the conclusion that it wasn't important afterall.

Of course you should add the insert date/time column now, so they can have it going forward.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Charmer SSChampion Points: 14903 More actions · Answer 9

Eric M Russell (2/20/2012)
Charmer (1/24/2012)
Hi Friends,
i have table which does not have any date time column, but my need is to get some records depending on the date&time...is it possible to get the records? or is there any other solution?
Please help me friends
Perhaps you can join to another table that has a date/time.
If this is a financial or security audit request, and your job depends on it, then perhaps the solution to your problem can start by querying the transaction log using the undocumented (but occasionally blogged about) fn_dblog function. You can try filtering on insert operations by object (table in your case) and [Begin Time] - [End Time]. If there is at least a sequential identifer column and you somehow determine the max / min value for a time frame, then you can use that to go back and query the table.
http://sqlskills.com/BLOGS/PAUL/post/Finding-out-who-dropped-a-table-using-the-transaction-log.aspx
This may end up being one of those situations where you go though the motions. step-by-step, and demonstrate to the requestor why it's not really possible and then hope they eventually come to the conclusion that it wasn't important afterall.
Of course you should add the insert date/time column now, so they can have it going forward.

Thank you buddy...it will be very help full to me for the future works...

Thanks,
Charmer