Hi all
Sorry if this is in the wrong place, please tell me if it is.
I've got a SQL 2000 database and I'm having problems selecting rows where
the value of a datetime column (with date and time) has to be less than
todays date with no time.
I can't work out how to do this. I've tried:
PrintedDate contains date and time
dDateOfVisit is todays date in dd/MM/yyyy format
SELECT DISTINCT Reference
FROM cdNotes
WHERE (StoreCode = 'CET719')
AND (ItemStatusId = 1)
AND convert(varchar, PrintedDate, 113) <= convert(datetime, '" &
dDateOfVisit & "', 103)
but it doesn't return any rows.
As I can't figure this out I decided to try and remove the times from the
datetime columns in my database. And to change the triggers to only insert
dates with no times.
CREATE TRIGGER [stsDateInsert] ON dbo.cdNotes
AFTER INSERT
AS
update cdNotes set DownloadedDate = GETDATE() where [index] in
(
SELECT inserted.[index] FROM inserted
)
Im having a bad day as I cant figure out how to remove the times from the
trigger update either.
Can someone please tell me how. Thanks very much.
Kind Regards
Darren RhymerDarren,
An expression that is true when the datetime column dt is
less than today's date with no time is
dt < dateadd(day,datediff(day,0,getdate()),0)
The messy expression on the right is simply today at midnight, as
a datetime type, so it's just what you want if you need today's
date "with no time part" in a trigger or elsewhere. You might also
consider this as a DEFAULT value for the column, which might
avoid the need for a trigger.
To get the date-only part of a different value than getdate(), you
can use the same idea: dateadd(day,datediff(day,0,anyDateTime),
0).
I won't guess just what you need to type into your query, but I will
mention that converting dDateOfVisit to a datetime expression would
be done with
convert(datetime,dDateOfVisit,103)
if that column is a varchar value with format dd/mm/yyyy. What you
have below tries to convert the string
"& dDateOfVisit & "
(with the double quotes, ampersands, spaces, and the word dDateOfVisit
as part of the string). That's not a date, and you should get an error. If
you don't, then you haven't shown us the exact query you are executing.
More likely than not, you'll have better luck if you can change your
table structure so that dates are stored in datetime columns, not in
string columns.
Your query doesn't seem to have anything to do with today's date,
so to select rows where a column value is "less than today's date"
you'll need getdate() somewhere in an expression like the one at
the top of this reply, but I don't know which of the two date columns
you need to compare with today.
Steve Kass
Drew University
Darren Rhymer wrote:
>Hi all
>Sorry if this is in the wrong place, please tell me if it is.
>I've got a SQL 2000 database and I'm having problems selecting rows where
>the value of a datetime column (with date and time) has to be less than
>todays date with no time.
>I can't work out how to do this. I've tried:
>PrintedDate contains date and time
>dDateOfVisit is todays date in dd/MM/yyyy format
>SELECT DISTINCT Reference
>FROM cdNotes
>WHERE (StoreCode = 'CET719')
>AND (ItemStatusId = 1)
>AND convert(varchar, PrintedDate, 113) <= convert(datetime, '" &
>dDateOfVisit & "', 103)
>but it doesn't return any rows.
>As I can't figure this out I decided to try and remove the times from the
>datetime columns in my database. And to change the triggers to only insert
>dates with no times.
>CREATE TRIGGER [stsDateInsert] ON dbo.cdNotes
>AFTER INSERT
>AS
>update cdNotes set DownloadedDate = GETDATE() where [index] in
>(
>SELECT inserted.[index] FROM inserted
> )
>Im having a bad day as I cant figure out how to remove the times from the
>trigger update either.
>Can someone please tell me how. Thanks very much.
>Kind Regards
>Darren Rhymer
>|||Darren Rhymer (darren_rhymer@.hotmail.com.no.spam) writes:
> Sorry if this is in the wrong place, please tell me if it is.
> I've got a SQL 2000 database and I'm having problems selecting rows where
> the value of a datetime column (with date and time) has to be less than
> todays date with no time.
> I can't work out how to do this. I've tried:
> PrintedDate contains date and time
> dDateOfVisit is todays date in dd/MM/yyyy format
> SELECT DISTINCT Reference
> FROM cdNotes
> WHERE (StoreCode = 'CET719')
> AND (ItemStatusId = 1)
> AND convert(varchar, PrintedDate, 113) <= convert(datetime, '" &
> dDateOfVisit & "', 103)
I assume that this is a SELECT send an SQL statement from a client,
the apparence of & and # indicate so.
Never embed values directly into the SQL command, but use parameter
markers instead, and then use a parameter object. The client API will
then convert the date according to the regional settings and pass the
date as a binary value, then you will not have to use convert for the
date value on the SQL Server side.
As I don't know which client API you are using I cannot give the exact
details on how to do this.
You should also avoid wrapping columns into expressions, as this preclude
use of the any index on the column, or at least the best use of the index.
Why convert the date to format 113 is beyond me, as this format includes
the time portion as well. If you want to say:
WHERE (date porttion of PrintedDate) <= dDateOfVisit
this is better:
WHERE PrintedDate < dateadd(DAY,1, @.dDateofVisit)
> As I can't figure this out I decided to try and remove the times from the
> datetime columns in my database.
If you do not need the times that's an excellent idea!
The idiom to strip a datetime value of a its time is
convert(char(8), value, 112)
Format 112 is YYYYMMDD which has the property of always being interpreted
in the one and same way in SQL Server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi chaps
Thanks very much for the repsonses they've been most interesting. I must
now apoligise for me leading you somewhere in the wrong direction on the 1st
part.
1) This is where Im trying to select from the database all records with
databasedate <= specified date. I failed to say, sorry, that this is being
done in VB.NET. My sql query looks like:
sql = "SELECT distinct reference from CDNotes"
sql = sql & " WHERE StoreCode = '" & txtStoreCode.Text
sql = sql & "' AND ItemStatusId = " & ItemStatus.Printed
sql = sql & " AND PrintedDate < dateadd(DAY, 1, '" & dDateOfVisit & "')"
which returns THE CORRECT RESULTS. (Thanks Erland for spotting this)
2) This is where im trying to remove the times from the existing datetime
columns in the database.
I have
UPDATE cdNotes
SET DownloadedDate = CONVERT(char(8), DownloadedDate, 112)
which works, thanks very much.
3) I need to remove the time part from the datetime when I'm inserting
GETDATE into the column after the status has changed.
CREATE TRIGGER [stsDateChange] ON dbo.cdNotes
AFTER UPDATE
AS
update cdNotes set PrintedDate = CONVERT(char(8), GETDATE(), 112) where
[index] in
(
SELECT inserted.[index] FROM inserted JOIN deleted ON inserted.[index]
=deleted.[index]
WHERE inserted.itemStatusId <> deleted.itemStatusId
AND inserted.itemStatusId = 2
)
update cdNotes set ScannedDate = CONVERT(char(8), GETDATE(), 112) where
[index] in
(
SELECT inserted.[index] FROM inserted JOIN deleted ON inserted.[index]
=deleted.[index]
WHERE inserted.itemStatusId <> deleted.itemStatusId
AND (inserted.itemStatusId = 3 OR inserted.itemStatusId = 4)
)
This now works aswell.
Many thanks to you two, cheers
Darren Rhymer
"Darren Rhymer" wrote:
> Hi all
> Sorry if this is in the wrong place, please tell me if it is.
> I've got a SQL 2000 database and I'm having problems selecting rows where
> the value of a datetime column (with date and time) has to be less than
> todays date with no time.
> I can't work out how to do this. I've tried:
> PrintedDate contains date and time
> dDateOfVisit is todays date in dd/MM/yyyy format
> SELECT DISTINCT Reference
> FROM cdNotes
> WHERE (StoreCode = 'CET719')
> AND (ItemStatusId = 1)
> AND convert(varchar, PrintedDate, 113) <= convert(datetime, '" &
> dDateOfVisit & "', 103)
> but it doesn't return any rows.
> As I can't figure this out I decided to try and remove the times from the
> datetime columns in my database. And to change the triggers to only inser
t
> dates with no times.
> CREATE TRIGGER [stsDateInsert] ON dbo.cdNotes
> AFTER INSERT
> AS
> update cdNotes set DownloadedDate = GETDATE() where [index] in
> (
> SELECT inserted.[index] FROM inserted
> )
> Im having a bad day as I cant figure out how to remove the times from the
> trigger update either.
> Can someone please tell me how. Thanks very much.
> Kind Regards
> Darren Rhymer|||The fastest way to strip date is:
DATEADD(DAY, DATEDIFF(DAY, <DateTimeGoesHere>, 0), <DateTimeGoesHere> )
<DateTimeGoesHere> can be a column name, variable, or getdate().
Both <DateTimeGoesHere> have to be the same. This formula calculates the
number of days from your datetime to 19000101. (Due to the parameter
ordering, this is a negative number.) Then, it adds the negative number
(i.e. subtracts it) from your datetime leaving only the time.
The fastest way to strip time is:
CAST(DATEDIFF(DAY,0, <DateTimeGoesHere> ) as datetime)
<DateTimeGoesHere> can be a column name, variable, or getdate().
If you want today at midnight:
CAST(DATEDIFF(DAY,0,getdate()) as datetime)
If you want tomorrow at midnight:
CAST(DATEDIFF(DAY,0,getdate())+1 as datetime)
(This can be used to include data that occurred anytime today, regardless of
time.)
When dealing with datetimes, always consider both portions. If the client
sends a date and you want anything that occurs on that date:
dtColumn >= @.MyDate and
dtColumn < CAST(DATEDIFF(DAY,0,@.MyDate)+1 as datetime)
Had the client sent the date and time, then:
dtColumn >= CAST(DATEDIFF(DAY,0,@.MyDate) as datetime) and
dtColumn < CAST(DATEDIFF(DAY,0,@.MyDate)+1 as datetime)
Other posters are correct, you want to avoid wrapping functions around a
column name in a WHERE clause if possible.
And, you should avoid converting datetime to a character string (i.e.
char(8)) and then converting it to datetime containing just a date or a time
.
It's not effecient.
Hope that helps,
Joe
"Darren Rhymer" wrote:
> Hi all
> Sorry if this is in the wrong place, please tell me if it is.
> I've got a SQL 2000 database and I'm having problems selecting rows where
> the value of a datetime column (with date and time) has to be less than
> todays date with no time.
> I can't work out how to do this. I've tried:
> PrintedDate contains date and time
> dDateOfVisit is todays date in dd/MM/yyyy format
> SELECT DISTINCT Reference
> FROM cdNotes
> WHERE (StoreCode = 'CET719')
> AND (ItemStatusId = 1)
> AND convert(varchar, PrintedDate, 113) <= convert(datetime, '" &
> dDateOfVisit & "', 103)
> but it doesn't return any rows.
> As I can't figure this out I decided to try and remove the times from the
> datetime columns in my database. And to change the triggers to only inser
t
> dates with no times.
> CREATE TRIGGER [stsDateInsert] ON dbo.cdNotes
> AFTER INSERT
> AS
> update cdNotes set DownloadedDate = GETDATE() where [index] in
> (
> SELECT inserted.[index] FROM inserted
> )
> Im having a bad day as I cant figure out how to remove the times from the
> trigger update either.
> Can someone please tell me how. Thanks very much.
> Kind Regards
> Darren Rhymer|||P.S. Seeing we're talking about dates and times...
DateTime of 23:59:59.997 is the highest stored time value.
23:59:59.998 is rounded down to .997.
23:59:59.999 is rounded up to the next day.
Just be careful if you concatenate a datetime range such as
>= 00:00:00.000 and <= 23:59:59.999 as the .999 will round the datetime up to the ne
xt day at midnight.|||Joe
Thanks very much for you reply. Makes interesting reading.
At the moment one of my triggers to update the scanned date column with
todays date looks like:
update cdNotes set ScannedDate = CONVERT(char(8), GETDATE(), 112) where
[index] in
(
SELECT inserted.[index] FROM inserted JOIN deleted ON inserted.[index]
=deleted.[index]
WHERE inserted.itemStatusId <> deleted.itemStatusId
AND (inserted.itemStatusId = 3 OR inserted.itemStatusId = 4)
)
Are you saying I should somehow replace the
CONVERT(char(8), GETDATE(), 112)
with
DATEDIFF(DAY,0, GETDATE()
Thanks again
Darren
Kind Regards
Darren Rhymer
"Joe from WI" wrote:
> The fastest way to strip date is:
> DATEADD(DAY, DATEDIFF(DAY, <DateTimeGoesHere>, 0), <DateTimeGoesHere> )
> <DateTimeGoesHere> can be a column name, variable, or getdate().
> Both <DateTimeGoesHere> have to be the same. This formula calculates the
> number of days from your datetime to 19000101. (Due to the parameter
> ordering, this is a negative number.) Then, it adds the negative number
> (i.e. subtracts it) from your datetime leaving only the time.
> The fastest way to strip time is:
> CAST(DATEDIFF(DAY,0, <DateTimeGoesHere> ) as datetime)
> <DateTimeGoesHere> can be a column name, variable, or getdate().
> If you want today at midnight:
> CAST(DATEDIFF(DAY,0,getdate()) as datetime)
> If you want tomorrow at midnight:
> CAST(DATEDIFF(DAY,0,getdate())+1 as datetime)
> (This can be used to include data that occurred anytime today, regardless
of
> time.)
>
> When dealing with datetimes, always consider both portions. If the client
> sends a date and you want anything that occurs on that date:
> dtColumn >= @.MyDate and
> dtColumn < CAST(DATEDIFF(DAY,0,@.MyDate)+1 as datetime)
> Had the client sent the date and time, then:
> dtColumn >= CAST(DATEDIFF(DAY,0,@.MyDate) as datetime) and
> dtColumn < CAST(DATEDIFF(DAY,0,@.MyDate)+1 as datetime)
> Other posters are correct, you want to avoid wrapping functions around a
> column name in a WHERE clause if possible.
> And, you should avoid converting datetime to a character string (i.e.
> char(8)) and then converting it to datetime containing just a date or a ti
me.
> It's not effecient.
> Hope that helps,
> Joe
> "Darren Rhymer" wrote:
>|||Darren Rhymer (darren_rhymer@.hotmail.com.no.spam) writes:
> Are you saying I should somehow replace the
> CONVERT(char(8), GETDATE(), 112)
> with
> DATEDIFF(DAY,0, GETDATE())
Both yield the same result, so it's a matter of taste which one to choose.
I prefer the first, because, well, if you need to present the date in
some output, you can use the same format. Overall, when you work with
date literals, you use character strings, whereas using numeric types
for dates feels more akward to me. But it would be difficult to say
that these are any compelling reasons.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||With all due respect, in this example, the datetime is not being used as
ouput. Darren wants the strip the time from a datetime data type and update
a datetime column.
Someone ran performance tests on various datetime conversions. (I cannot
remember who and I appolize to the person who did it. If you search for dat
e
or datetime, you might be able to find the post and the link to the test
results.)
Converting a datetime to a string and then to a datetime again is one the
slowest performers. (I suggest you test it on your hardware in query
analyzer using a loop and variables.) Yes, we're talking microseconds per
execution but on a heavily loaded box it all adds up.
Based on those tests, the following is the fastest because it is pure
integer arithmetic which by the way is always the fastest way to do things i
n
a computer.
CAST(DATEDIFF(DAY,0, <DateTimeGoesHere> ) as datetime)
So yes, if you want the best performing code, the trigger should use
CAST(DATEDIFF(DAY,0,getdate()) as datetime)
(P.S. You need the outer cast as datetime because datediff returns an
integer value.)
Also, I have a correction to my earlier post where the client sends just the
date. The upper limit could and probably should do a simple dateadd.
dtColumn < DATEADD(DAY,1,@.MyDate)
Just my two cents,
Joe
"Erland Sommarskog" wrote:
> Darren Rhymer (darren_rhymer@.hotmail.com.no.spam) writes:
> Both yield the same result, so it's a matter of taste which one to choose.
> I prefer the first, because, well, if you need to present the date in
> some output, you can use the same format. Overall, when you work with
> date literals, you use character strings, whereas using numeric types
> for dates feels more akward to me. But it would be difficult to say
> that these are any compelling reasons.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||
Joe from WI wrote:
>With all due respect, in this example, the datetime is not being used as
>ouput. Darren wants the strip the time from a datetime data type and updat
e
>a datetime column.
>Someone ran performance tests on various datetime conversions. (I cannot
>remember who and I appolize to the person who did it. If you search for da
te
>or datetime, you might be able to find the post and the link to the test
>results.)
>
Here's the post you may be talking about :
http://groups.google.com/groups?q=b...100000+datetime
Steve Kass
Drew University
>Converting a datetime to a string and then to a datetime again is one the
>slowest performers. (I suggest you test it on your hardware in query
>analyzer using a loop and variables.) Yes, we're talking microseconds per
>execution but on a heavily loaded box it all adds up.
>Based on those tests, the following is the fastest because it is pure
>integer arithmetic which by the way is always the fastest way to do things
in
>a computer.
>CAST(DATEDIFF(DAY,0, <DateTimeGoesHere> ) as datetime)
>So yes, if you want the best performing code, the trigger should use
>CAST(DATEDIFF(DAY,0,getdate()) as datetime)
>(P.S. You need the outer cast as datetime because datediff returns an
>integer value.)
>Also, I have a correction to my earlier post where the client sends just th
e
>date. The upper limit could and probably should do a simple dateadd.
>dtColumn < DATEADD(DAY,1,@.MyDate)
>Just my two cents,
>Joe
>"Erland Sommarskog" wrote:
>
>
MrHat wrote:
hypercode: