how to make this simple query to query date without time
-
i got table that show like this
no date etc
1 08-09-2005 12:10:05.000 eating
when i use query like this
select * from table1 where date = '08-09-2005'
it return nothing
so how to make simple query like that ???
to make clear the question is how to query that table only with date only n ignore the clock so they return all data on that day
thank's
manik095
-
select * from table1 where date >= '08-09-2005' and date < '09-09-2005'
-
okay remi - you beat me to it - I was going to suggest converting the date first so that he could then use any date as filter (instead of >= & <)....manik - at least now you have several choices....
manik - first convert your date column to the format that you want and then use the where clause to compare....recommended format however, is ISO - read this other post on this site that has a similar question....
so you would do something like this....
ISO yymmdd format....
SELECT * FROM myTable WHERE CONVERT(CHAR(8), date, 112) = '20050809'
USA mm-dd-yy format....
SELECT * FROM myTable WHERE CONVERT(VARCHAR, date, 110) = '08-09-2005'
**ASCII stupid question, get a stupid ANSI !!!** -
thank's i have tried using
select * from table1 where date >= '08-09-2005' and date < '09-09-2005'
that's work.
but is there some function that work like day() or month(),
in this case will retur only date ??
thanks all
-
Yes there is :
dateadd(D, 0, datediff(d, 0, getdate()))
also don't use a function on a column on a where condition. You don't allow any indexes to be used in the query.
-
"also don't use a function on a column on a where condition. You don't allow any indexes to be used in the query"
Hmmm ... guess I've just got a super server then
Straight from Query Analyzer with table and field names changed to protect those who should not be spared. ( the liberal use of [] everywhere in the query should give you an idea though
)StmtText SELECT adv.[field1] , SUM(FLOOR(mth.[field2])) as [Amt1] , SUM(mth.[field3]) as [Amt2] FROM [DB].[dbo].[table1] adv INNER JOIN [DB].[dbo].[table2] mth ON adv.[table1id] = mth.[table1id] WHERE MONTH(mth.[field4]) = 9 AND YEAR(mth.[field4]) = 2005 GROUP BY adv.[field1]
(1 row(s) affected)
StmtText |--Compute Scalar(DEFINE: ([Expr1002]=If ([Expr1013]=0) then NULL else [Expr1014], [Expr1003]=If ([Expr1015]=0) then NULL else [Expr1016])) |--Stream Aggregate(GROUP BY: ([adv].[field1]) DEFINE: ([Expr1013]=COUNT_BIG(floor([mth].[field2])), [Expr1014]=SUM(floor([mth].[field2])), [Expr1015]=COUNT_BIG([mth].[field3]), [Expr1016]=SUM([mth].[field3]))) |--Sort(ORDER BY: ([adv].[field1] ASC)) |--Hash Match(Inner Join, HASH: ([mth].[table1Id])=([Expr1012]), RESIDUAL: ([Expr1012]=[mth].[table1Id])) |--Clustered Index Scan(OBJECT: ([DB].[dbo].[table2].[PK_table2] AS [mth]), WHERE: (datepart(month, [mth].[field4])=9 AND datepart(year, [mth].[field4])=2005)) |--Compute Scalar(DEFINE: ([Expr1012]=Convert([adv].[table1Id]))) |--Index Scan(OBJECT: ([DB].[dbo].[table1].[IX_table1_field1] AS [adv]))
(7 row(s) affected)
Ok, so it's doing index scans instead of seeks. Maybe that's what you meant.
--------------------
Colt 45 - the original point and click interface
-
Let me rephrase that. Won't allow the server to use an index SEEK.
-
Performance ahh who needs it.
its all good.
Performance is all in the users experience and perception.
You can have a subsecond query, but if the user "Feels" that its slow, then the perceived performance is Poor.
You can also have some mega search return in 2 seconds,
but if the user "Feels" its fast then perceived performance is Excelent. -
Ok.. every now and then I post part of this.. maybe it's time to post the whole enchilada. Some of this is mine.. some is attributed to those smarter than myself but I keep a whole series of code snippets in a script that I call DateTimeFun.
Be sure and read the explanations in the commented out section below - Contributed by Kay-Ole Behrmann
DECLARE -- this section by Mark Gelatt
@Today SmallDateTime,
@LastMonth SmallDateTime,
@EndLastMonth SmallDateTime,
@ThisMonth SmallDateTime,
@EndThisMonth SmallDateTime,
@NextMonth SmallDateTime,
@EndNextMonth SmallDateTime
SET @Today = DATEADD(d,DATEDIFF(d,0,GETDATE()),0) -- Strip off time elements.. setting the time to Midnight
SET @ThisMonth = DateAdd(dd,-Day(@Today),@Today) + 1 -- Subtract the Days from this month PLUS one day to find the 1st of the month..
SET @EndThisMonth = DATEADD(DAY, -1, DATEADD(Month,1,@ThisMonth))
SET @LastMonth = DATEADD(mm,-1,@ThisMonth)
SET @EndLastMonth = DATEADD(DAY, -1, @ThisMonth)
SET @NextMonth = DATEADD(DAY,1,@EndThisMonth)
SET @EndNextMonth = DATEADD(DAY, -1, DATEADD(MONTH,2,@ThisMonth))
Select Today = @Today, LastMonth = @LastMonth, EndLastMonth = @EndLastMonth,
EndThisMonth = @EndThisMonth, NextMonth = @NextMonth, EndNextMonth = @EndNextMonth
-- Strip off hours.. or other work arounds.. Contributed by Kay-Ole Behrmann
SELECT .... WHERE CONVERT(varchar, @DateVal, 101) = '08/01/2003'
SELECT .... WHERE @DateVal BETWEEN '2003-08-01' AND '2003-08-01 23:59:59.997'
SELECT .... WHERE @DateVal >= '2003-08-01' AND DateVal < '2003-08-02'
SELECT .... WHERE CAST(FLOOR(CAST(@DateVal AS float)) AS datetime)='8/1/2003'
SELECT CAST(FLOOR(CAST(getdate() AS float)) AS datetime)
select CAST(CAST(GetDate() AS INT) AS DATETIME)
-- this one should run faster... (and my personal favorite - mg)
select DATEADD(d,DATEDIFF(d,0,getdate()),0)
-- or convert to Text
SELECT CONVERT(Varchar(10),GetDate(),120)
SELECT CONVERT(Varchar(10),GetDate(),101)
/*
Contributed by Kay-Ole Behrmann
All of the above work to get records on 8/1/2003 regardless of the time of day.
The first solution converts to varchar format "101", i.e. US-Format mm/dd/yyyy, and compares to a string.
Doing it this way however, chances are you forget leading zeros (8/1/2003) and get no results.
The second solution uses knowledge about the largest possible timepart in a datetime, wich is not a particularly
pretty way.
The third way is the classic one, using two date-only values with an implicit time of 00:00:00.0000 and forces
a "greater or equal" to midnight 8/1 and a "less than" midnight 8/2.
Answer #4 cuts off the time part of the datetime value by removing decimals from the converted float-value.
This makes use of the fact that conversion to a float stores the date in the integer-part and time in the decimals.
*/
-- Calculate lapsed Time ONLY.. no date..
SELECT TimeLapse = LTRIM(RTRIM(CAST(DATEDIFF(hh, DATEADD(d,DATEDIFF(d,0,getdate()),0), getdate()) AS char(10)))) + ':' +
CAST(DATEDIFF(mi, DATEADD(d,DATEDIFF(d,0,getdate()),0), getdate()) % 60 AS Char(2))
Hope this helps... someone... Mark
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply