DateTime variables

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:
>
>

Datetime convert problem

i am using select CONVERT(varchar(15),getdate(),101)
result is 1/23/2006 but i want result in 01/23/2005
what am i doing wrong. Any db settings?I'd suggest you use the ISO standard date 112 instead, eg:-
select CAST(CONVERT(char, GETDATE(), 112) AS datetime)
Here's why :-
SET language english
SELECT ISDATE('31 Jan 2005')
SELECT ISDATE('20050131')
GO
SET language french
SELECT ISDATE('31 Jan 2005')
SELECT ISDATE('20050131')
GO
HTH. Ryan
"Mubashir Khan" <m@.n.com> wrote in message
news:%23vPXuIDIGHA.2212@.TK2MSFTNGP15.phx.gbl...
>i am using select CONVERT(varchar(15),getdate(),101)
> result is 1/23/2006 but i want result in 01/23/2005
> what am i doing wrong. Any db settings?
>|||But i need date in MM/dd/yyyy format.
cant use 112 which is yyyyMMdd
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:%23v29kODIGHA.1188@.TK2MSFTNGP14.phx.gbl...
> I'd suggest you use the ISO standard date 112 instead, eg:-
> select CAST(CONVERT(char, GETDATE(), 112) AS datetime)
> Here's why :-
> SET language english
> SELECT ISDATE('31 Jan 2005')
> SELECT ISDATE('20050131')
> GO
>
> SET language french
> SELECT ISDATE('31 Jan 2005')
> SELECT ISDATE('20050131')
> GO
>
> --
> HTH. Ryan
> "Mubashir Khan" <m@.n.com> wrote in message
> news:%23vPXuIDIGHA.2212@.TK2MSFTNGP15.phx.gbl...
>|||In that case this may be of use to you :-
http://www.aspfaq.com/show.asp?id=2460
HTH. Ryan
"Mubashir Khan" <m@.n.com> wrote in message
news:Ox8PvWDIGHA.3944@.tk2msftngp13.phx.gbl...
> But i need date in MM/dd/yyyy format.
> cant use 112 which is yyyyMMdd
> "Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:%23v29kODIGHA.1188@.TK2MSFTNGP14.phx.gbl...
>|||"Mubashir Khan" <m@.n.com> wrote in message
news:%23vPXuIDIGHA.2212@.TK2MSFTNGP15.phx.gbl...
>i am using select CONVERT(varchar(15),getdate(),101)
> result is 1/23/2006 but i want result in 01/23/2005
> what am i doing wrong. Any db settings?
It works on sql server 2000. Perhaps this is a problem with the tool you
are using to view the results.|||no use
<snip>
WHEN 'MM/DD/YYYY' THEN
CONVERT(CHAR(10), @.dt, 101)
</snip>
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:Oh0EueDIGHA.216@.TK2MSFTNGP15.phx.gbl...
> In that case this may be of use to you :-
> http://www.aspfaq.com/show.asp?id=2460
> --
> HTH. Ryan
> "Mubashir Khan" <m@.n.com> wrote in message
> news:Ox8PvWDIGHA.3944@.tk2msftngp13.phx.gbl...
>|||it works fine on query analyzer
but it doesnot work when it is placed in a trigger.
trigger files it in some db table :'?
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%23SfFqgDIGHA.1676@.TK2MSFTNGP09.phx.gbl...
> "Mubashir Khan" <m@.n.com> wrote in message
> news:%23vPXuIDIGHA.2212@.TK2MSFTNGP15.phx.gbl...
> It works on sql server 2000. Perhaps this is a problem with the tool you
> are using to view the results.
>|||> it works fine on query analyzer
> but it doesnot work when it is placed in a trigger.
> trigger files it in some db table :'?
That wasn't the question you asked. The responses you have received assume
that you are having problems with the output formats - that, indeed, is not
the problem at all. Perhaps you need to read the following:
http://www.aspfaq.com/show.asp?id=2023
Tibor has some information that explains this in a format that is more
user-friendly, but it appears to be down. Have a look at it (with respect
to datetime values) when it comes back up.
http://www.karaszi.com/sqlserver/articles.asp
If that does not clear up the problem, then define "doesn't work".

Dates in an update statement

Hi

This is really bugging me. All I want to do is add a date in an update statement but when I do it, its adding the wrong date e.g.

here is my update statement trying to update a date field

update tbl_xset [date]=01/02/2006where id = 1

when i do this the data comes back as 1900-01-01 00:00:00.000, am I doing something wrong, what is the correct way of doing this. Thanks.

How about changing that to:

update tbl_xset [date] ='01/02/2006'where id = 1

|||try using thisupdate tbl_x set [date]='01/02/2006' where id = 1-------do not forget to mark the post as answered if it helped you|||

thanks guys that worked

DateRange Query give wrong results

I am running some query with the following where clause

where DateCreated BETWEEN '06/01/2004' AND '06/30/2004'

It gives the records where DateCreated is of 2004-07-01 21:48:02.377

the default language on the server is British English.

Could anybody please tell me the reason why records from july are also comingBecause '06/30/2004' isn't a valid British date (no calendar has 30 months), I would guess that SQL Server assumes you want to compare string values. How 2004-07-01 got included is beyond me, but if you post the code that is causing the problem I might be able to offer more ideas.

-PatP

DateDiff + Where am I going wrong?

Hi,

I'm creating a sample report in Visual Studio 2005 to be used in SSRS 2005.

I'm attempting to do some conditional formatting like so:

=IIF(DateDiff("Day", Fields!Jobseeker.Last Interview.Value, NOW()) > 14, "Red", "Transparent"

When ever I try and preview the report, VS throws a wobblie and closes down. The format of the Last Interview column is dd/mm/yyyy.

Any suggestions

Thanks, Steve

Hi, Steve,

Use VB parameters to DateDiff - ... try 'd' instead of "Day".

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctdatediff.asp

Also verify that the "Last Interview" field name has an underscore "Last_Interview" - that should have happened automatically when you created a dataset from the query.

_

This posting is provided "AS IS" with no warranties, and confers no rights.