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

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Query using Date or Time or Both

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-15 : 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

1408 Posts

Posted - 2002-07-15 : 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
Go to Top of Page

MansiTrehan
Starting Member

1 Post

Posted - 2008-04-15 : 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
Go to Top of Page
   

- Advertisement -