Query test for just date of smalldatetime
-
We use smalldatetime fields to represent when a patient or doctor filled out a form. I need to match these fields based on just the MM/DD/YYYY part. Is there a function that will allow me to do this?
Thanks all!
-
Select * from dbo.MyTable where
dateadd (d, 0, datediff(d, 0, MyDateField))
=
CAST(dateadd (d, 0, datediff(d, 0, getdate())) AS SMALLDATETIME)
-
No pretty way to do it.
DECLARE @BeginDate as datetime
DECLARE @EndDate as datetime
select @BeginDate = CAST(cast(right('0000' + cast(datepart(year,getdate()) as varchar),4) as NVarchar) + '-' +
cast(right('00' + cast(datepart(month,getdate()) as varchar),2) as NVarchar) + '-' +
cast(right('00' + cast(datepart(day,getdate()) as varchar),2) as NVarchar) +
CAST (' 00:00' AS VARCHAR ) AS SMALLDATETIME)
select @EndDate = CAST(cast(right('0000' + cast(datepart(year,getdate()) as varchar),4) as NVarchar) + '-' +
cast(right('00' + cast(datepart(month,getdate()) as varchar),2) as NVarchar) + '-' +
cast(right('00' + cast(datepart(day,getdate()) as varchar),2) as NVarchar) +
CAST (' 23:59' AS VARCHAR ) AS SMALLDATETIME)
print @BeginDate
print @EndDate
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
I think that my version (well Frank Kalis' version) is prettier and actually will run much faster on larger datasets... it's just a matter of understanding what's actually happening under the hood.
-
The catch is that the smalldatetime still has the hours:minutes in it. If the data is stored as smalldatetime you have to do a between. Or the where clause has to extract the date part only in the dataset and compare it to the extracted getdate() with the time set to 00:00.
The other thought is to put a update/insert trigger on the table that automatically strips the hour to 00:00. We had to do that to one app/database we wrote to make it easier.
Just my $0.02.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
Have you actually run this statement??
select CAST(dateadd (d, 0, datediff(d, 0, getdate())) AS SMALLDATETIME)
-
CAST(dateadd (d, 0, datediff(d, 0, getdate())) AS SMALLDATETIME)
This CAST is not needed I think, since you've already set the time to midnight and are only interested in the date stuff anyway. DATETIME and SMALLDATETIME are somewhat compatible.
Chuck,
if you can make sure, you are never interested in the time portion of your SMALLDATETIME column, I would consider running an UPDATE to set the time of the existing data to midnight and to make sure the insertion process only delivers the date and not both date and time.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Why not just use BETWEEN?
if object_id('dbo.MyTable') is not null
drop table dbo.MyTable
create table MyTable
(RowDate smalldatetime not null
,Col1 int
,Col2 int
,Col3 int
)
insert dbo.MyTable
select '4/1/2005 08:00', 1,2,3
UNION
select '4/1/2005 09:00', 1,5,6
UNION
select '4/2/2005 08:00', 1,2,7
UNION
select '4/3/2005 13:00', 1,8,8
declare @Date smalldatetime
set @Date = '4/1/2005'
select *
from MyTable
where RowDate between @Date and @Date + 1
Scott Thornburg
-
Hi,
This returns a date sans the time. Since it returns a smalldatetime, there is going to be a time component. In this case 00:00:00; no way around that. If you want ONLY yy-mm-dddd, you would have to make it a string.
/* This accepts datetime as well as smalldatetime dates, strips them of their time component, and returns them.
*/
CREATE function fn_DateOnly (@IncomingDate datetime)
returns smalldatetime
as
begin
declare @DateOnly smalldatetime
set @DateOnly = (select DATEADD(d,DATEDIFF(d,0, @IncomingDate),0))
Return (@DateOnly)
end
Teague
-
This solution has already been posted, also if you only want the date portion you're still better off using dates than strings as they will compare must faster than varchars.
-
Sorry,
I just scanned the responses for the word "function".
teague
-
You can put the Convert into the join expression to evaluate simply based on the day instead of the day-time initiallly represented by a smalldatetime value...
Declare @Dateparm smalldatetime
Set @dateparm = '1/2/2005'
Select @dateparm
---versus
Select Convert(char(10), @dateparm, 101)
ciao ciao -
Well I was writing a reply, got up for an hour or so, finished it, hit post and it did not work so here we go again.
Thanks for all these great possibilities. I think the one proposed by Frank and Remi will work just fine for my needs.
Let me clarify what is happening. A doctor tests a patient at 8:30 AM. The patient then takes their Parkinson's meds and gets tested again when they kick in at say 9:15 AM. Each has a distinct record in our database.
The patient is then given a test related to a testosterone study at say 12:30 PM. I need to find all of the tests above that happened on the same dates as these testosterone tests.
Again I think this will work fine:
dateadd (d, 0, datediff(d, 0, MyDateField))
Let me see if I have what it does straight: datediff returns the integer number of days since "day 0" for the smalldatetime value in question. dateadd takes that integer, adds it to 0, then converts it back to a smalldatetime. The result is 3/31/05 12:00 AM. Is that correct?
If so, I think Frank is right about an additional cast not being necessary.
I'll post a portion of the query so you can see how it goes.
Thanks all!
PS - Let me note that mySQL has commands to convert dates back and forth between several different formats built-in.
-
Finished query works like a charm. Thanks for the elegant solution!
select MRN, PTFirst, PTLast, UPDRS.dtmevaluation, blnmedications, MBMScore,
ADLScore, CTScore, dblStage, DyskinesiaR, DyskinesiaL, MOTORscore
from tblpatient p join viewALLUPDRSScores UPDRS on UPDRS.idpatient=p.idpatient
where EXISTS
(SELECT *
FROM tbltestosterone1 t1
WHERE t1.idpatient = p.idpatient and
dateadd(d,0, datediff(d, 0, t1.dtmvisit)) =
dateadd(d,0, datediff(d, 0, UPDRS.dtmevaluation))
)
ORDER BY ptlast, ptfirst, dtmevaluation
-
Let me see if I have what it does straight: datediff returns the integer number of days since "day 0" for the smalldatetime value in question. dateadd takes that integer, adds it to 0, then converts it back to a smalldatetime. The result is 3/31/05 12:00 AM. Is that correct?
Yes, that is essentially the whole "trick". Kinda frustratingly simple. Just one addition. DATEDIFF only considers day boundaries (or whatever data parameter you've set).
SELECT
DATEDIFF(d,'20050228 23:59:59.997', '20050301 00:00:00.000')
-----------
1
(1 row(s) affected)
Nobody would seriously say, 1 day has elapsed between both dates. But DATEDIFF only looks at the datepart and so has no other chance but to state that 1 day is between both dates.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply