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.
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 21/1/2000 7:15:00 PM 3 41/2/2000 3:10:00 PM 5 61/2/2000 7:15:00 PM 7 81/3/2000 5:15:00 AM 9 10I 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 EntriesI 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 EntriesHowever,SELECT * from testtable where dt = '1/1/2000 7:15 PM'does return:dt ch0 ch1---------------------------------------1/1/2000 7:15:00 PM 3 4How 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' andSELECT * 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
1 Post |
Posted - 2008-04-15 : 13:21:35
|
HiIam 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] ))>=10and (datediff(dd,T0.[createDate],T0.[closeDate]))>=1Here 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 |
 |
|
|
|
|
|
|