Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Query using Date or Time or Both
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ask SQLTeam Question

0 Posts

Posted - 07/15/2002 :  08:29:04  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Gabe writes "I have a website that runs off a SQL Server 2000 Desktop Engine database(8.00.194 RTM) on Windows 2000 Server(SP2), and in that database is a table that holds the date, time, and data for that row entry(table columns are actually |d|t|ch0|ch1|etc.....).

The reason I have been using a different column for the date and the time is that the queries that run against the database must be able to pull entries by a specific date(all entries for that day), or a specific time(all entries for that time spanning days or years), or a single entry that is a combination of both(unique point in time).

I have been experimenting by combining the two date/time columns together(in a test table) into a single datetime column, but I cant seem to get the querys to work.

Say I have 5 entries:

dt                                ch0     ch1
1/1/2000 1:15:00 PM       1        2
1/1/2000 7:15:00 PM       3        4
1/2/2000 3:10:00 PM       5        6
1/2/2000 7:15:00 PM       7        8
1/3/2000 5:15:00 AM       9        10

I want to pull up all entries at 7:15 PM, so I use:
SELECT * from testtable where dt = '7:15:00 PM'
And I get:

dt                                ch0     ch1
0 Entries

I want to pull up all entries on 1/1/2000, so I use:
SELECT * from testtable where dt = '1/1/2000'
And I get:

dt                                ch0     ch1
0 Entries

SELECT * from testtable where dt = '1/1/2000 7:15 PM'
does return:

dt                                ch0     ch1
1/1/2000 7:15:00 PM       3        4

How can I make the time only and the date only queries work?"

A custom title

United Arab Emirates
1408 Posts

Posted - 07/15/2002 :  08:52:13  Show Profile  Reply with Quote

sELECT * from testtable where substring(convert(varchar(22),dt,0),12,8) = '7:15:00 PM'


SELECT * from testtable where substring(convert(varchar(22),dt,101),1,12) = '1/1/2000'

Play with Convert and substring function you should be able to get thru your requirements easily.

What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson
Go to Top of Page

Starting Member

1 Posts

Posted - 04/15/2008 :  13:21:35  Show Profile  Reply with Quote
Iam in big trouble here and would appreciate some solution ASAP. My problem here is--
In a table i hv been asked to write query for " Average ATurn around Time for Service Callsmore than 10 min":- What i hv written is
SELECT T0.[callID], T0.[customer], T0.[subject], T0.[itemName], T0.[status], T0.[createDate], T0.[createTime], T0.[closeDate],
T0.[closeTime],(datediff(dd,T0.[createTime] ,T0.[closeTime])) as "Differnce HH:MM"
FROM OSCL T0 WHERE T0.[status] = '-1'
and (datediff(mm,T0.[createTime],T0.[closeTime] ))>=10
and (datediff(dd,T0.[createDate],T0.[closeDate]))>=1

Here its calculating n getting the data for customers where the service calls ended in 1 day but its not calculating correct data where the day changes, lets say Cretaion date is 12/3/07 n Closing date is 12/5/ the time calcualtion shud be for 2 days.
Please suggest me some changes to get overall calcualtion for same day as well as different days.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000