DateTime Query

Question

DateTime Query

SQL2DAY

SSC Veteran

Points: 233
More actions
May 31, 2007 at 7:08 pm

#372101

I want to query my database for all records that have a time of 11:59:59 PM from the DateTime column. How do I accomplish this?
sql2day

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

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

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 1

With a fair amount of dynamic SQL...

Do you mean you want to do an "auto discovery" of each table having any DateTime column (even if more than 1 per table) for all tables in the database? Why do you want to do this?

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

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 2

Just check if the given code below does work for you.

select substring(convert(varchar(30),getdate(),109),13,len(convert(varchar(30),getdate(),109)))

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

sql2day

Here's a stub query

SELECT * FROM TableName t
WHERE DATEPART (hh, t.DateTimeColumn) = '23'
 AND DATEPART (mm, t.DateTimeColumn) = '59'
 AND DATEPART (ss, t.DateTimeColumn) = '59'

but as Jeff pointed out, if you want to scan the DateTime column (if one exists) for each table in the db, then there's quite a lot more to add to this.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Shawn McGehee Ten Centuries Points: 1239 More actions · Answer 4

From your post I get the idea that you want to query the whole database (i.e. each table in the database). If this is the case, you can use the query posted above with the sp_msforeachtable stored procedure. It takes a parameter @command and you can reference the database within the command using the ? symbol.

This is also assuming that each table in the database will have a column with the same name (i.e. dateTimeColumn).

sp_msforeachtable 'SELECT * FROM ? t WHERE DATEPART (hh, t.DateTimeColumn) = '23' AND DATEPART (mm, t.DateTimeColumn) = '59' AND DATEPART (ss, t.DateTimeColumn) = '59'

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

Nicely done... still going to take a fair bit more than that if table has more than 1 DateTime column, has no DateTime column, or, as you said, has different names for DateTime columns.

But I want to find out what the OP has in mind before I dedicate any more time to this...

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

Con Alexis Old Hand Points: 331 More actions · Answer 6

Don't know if this is still relevant for you, but output of following query will give you the required SQL statements:

SELECT 'SELECT * FROM [' + sysU.name + '].[' + sysO.name + '] WHERE CONVERT(varchar(8), [' + sysC.name + '], 108) = ''11:59:59'''

FROM

dbo.syscolumns sysC INNER JOIN

dbo.sysobjects sysO ON sysC.id = sysO.id AND sysO.xtype = 'U' INNER JOIN

dbo.sysusers sysU ON sysO.uid = sysU.uid

WHERE sysC.xtype IN (58, 61)