time function

Question

Post reply

time function

biren

Right there with Babe

Points: 719
More actions
May 5, 2010 at 2:30 pm

#222221

please advice anybody in here
I have select * table_name WHERE condition and condition and DATEDIFF()
in that datediff() function I want to add a function in such a way that it will display select products within 1 hour, is this possible??

Viewing 14 posts - 1 through 13 (of 13 total)

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

rjv_rnjn Hall of Fame Points: 3023 More actions · Answer 1

Try (code taken from BOL)

DATEDIFF(hour, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

But you'll have to another condition to make sure the hour difference is only for the current day.

BOL: http://msdn.microsoft.com/en-us/library/ms189794.aspx

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

Keep it simple, keep it sargeable, keep it fast...

WHERE SomeDateTimeColumn >= DATEADD(hh,-1,GETDATE())

I'd also recommend against using any version of the 23:59:59.xxxxxxx stuff.

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

biren Right there with Babe Points: 719 More actions · Answer 3

well I dont have time stamp in my table I want to generate resultset using select and providing condition like what happened in last hour

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

Unless you have some indication of time in the table or you run the job once an hour automatically whilst capturing the value of an IDENTITY column as a marker for the start of the next run, then I don't believe it can be done.

Heh... and you don't need to PM me for stuff like this... I get notified and if I have the time, I'll respond.

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

biren Right there with Babe Points: 719 More actions · Answer 5

biren

Right there with Babe

Points: 719

May 5, 2010 at 3:34 pm

#1162844

thanks, I understand that

adalberto-339588 SSC Veteran Points: 261 More actions · Answer 6

adalberto-339588

SSC Veteran

Points: 261

May 5, 2010 at 5:02 pm

#1162873

Let me know if it work

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

adalberto-339588 (5/5/2010)
Let me know if it work

Yes... that would work if there were a date column. The posted problem is quite a bit different... the OP has no date column in his table.

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

matzer SSCrazy Eights Points: 9727 More actions · Answer 8

Jeff Moden (5/5/2010)
Unless you have some indication of time in the table or you run the job once an hour automatically whilst capturing the value of an IDENTITY column as a marker for the start of the next run, then I don't believe it can be done.
Heh... and you don't need to PM me for stuff like this... I get notified and if I have the time, I'll respond.

I don't have any experience with it, but SQL2K8 contains two new features to capture data changes. From BOL:

SQL Server 2008 provides the following new technologies that application developers can use to track data changes and enable applications to query for only the data that has changed:
* Change data capture
* Change tracking

Maybe worth to look at.

Peter

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

I'll have to remember that as one of the things to check on when I install my nice new copy of 2k8 Dev Ed. Thanks, Peter.

Now that I see your suggestion and the requirement, it almost sounds like we might be answering one of "those" interview questions.

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

rjv_rnjn Hall of Fame Points: 3023 More actions · Answer 10

That's a nice feature, but it's sad that I'm still using SQL2K5. There are quite a few new features that I get introduced to by looking at many interview questions.

Change data: I read through the MSDN articles a bit, but do not see any mention of where are they stored. It seems the default prune date is 3 days, so doesn't seem like it was designed for storing historical data on a large scale (or are they preventing abuse by making sure you know for how many days you're storing the change store).

biren Right there with Babe Points: 719 More actions · Answer 11

I used one of the column having date and ADDDATE so it works... thanks for reply

matzer SSCrazy Eights Points: 9727 More actions · Answer 12

rjv_rnjn (5/6/2010)
Change data: I read through the MSDN articles a bit, but do not see any mention of where are they stored. It seems the default prune date is 3 days, so doesn't seem like it was designed for storing historical data on a large scale (or are they preventing abuse by making sure you know for how many days you're storing the change store).

As far as I've understood, if you enable CDC on a specific table an additional (I'm not sure but probably hidden) table is created which can be accessed through a couple of functions. This table is filled with the changes to the original table taken from the transaction log by some background process. Note that CDC was designed especially to easily capture changed data on a regular basis by an ETL process in a data warehouse environment and not as a feature for storing historical data.

Peter

rjv_rnjn Hall of Fame Points: 3023 More actions · Answer 13

Peter Brinkhaus (5/6/2010)
Note that CDC was designed especially to easily capture changed data on a regular basis by an ETL process in a data warehouse environment and not as a feature for storing historical data.
Peter

Seeing from that perspective makes more sense (I missed/didn't notice that). Thanks for that, Peter.