| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
 Posted - 07/15/2002 : 08:29:04
|
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
However,
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?" |
|
|
Nazim
A custom title
United Arab Emirates
1408 Posts |
Posted - 07/15/2002 : 08:52:13
|
sELECT * from testtable where substring(convert(varchar(22),dt,0),12,8) = '7:15:00 PM'
and
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
|
 |
|
|
MansiTrehan
Starting Member
USA
1 Posts |
Posted - 04/15/2008 : 13:21:35
|
Hi
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/07...so 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.
Thanks |
|
|
| |
Topic |
|
|
|
Query using Date or Time or Both