DateTime types and getdate() comparison

SQL 2000. Let's say column MyDate is a datetime type. Is this
comparison syntax OK as is?
... where MyDate <= getdate()
Or is some formatting of the column value and/or of the function's
return value required for the comparison to work?
Thanks
LiamComparison operators (<,>,=, <>, >=, <= ) are allowed between two values wit
h
a datatype of datetime. Your expression is fine.
However, if you want to do things like add or subtract datetime values, you
will need to use the date and time functions in SQL Server.
"Liam" wrote:

> SQL 2000. Let's say column MyDate is a datetime type. Is this
> comparison syntax OK as is?
> .... where MyDate <= getdate()
> Or is some formatting of the column value and/or of the function's
> return value required for the comparison to work?
> Thanks
> Liam
>|||depends on what you need
but don't convert the column - you'll lose any sargability if it's indexed.
i tend not to try to rely on date data having being inserted with a time
of midnight, so i convert the variable and perform range queries
if you need mydate <= just the date: then do
MyDate < tomorrow at midnight
e.g.
where MyDate < dateadd(day, datediff(day, 0, getdate()), 0)+1
or if you need MyDate for just today
where MyDate >= dateadd(day, datediff(day, 0, getdate()), 0)
and MyDate < dateadd(day, datediff(day, 0, getdate()), 0)+1
or if you need mydate <= current date and time, then simply using
getdate() is appropriate.
Liam wrote:
> SQL 2000. Let's say column MyDate is a datetime type. Is this
> comparison syntax OK as is?
> ... where MyDate <= getdate()
> Or is some formatting of the column value and/or of the function's
> return value required for the comparison to work?
> Thanks
> Liam

datetime diff query syntax

Hi.
I'm trying but not getting correct results.

I have two tables
one with app, msg, time
(varchar,datetime,varchar)

app1 start 2006-04-03 13:33:36.000
app1 stuff 2006-04-03 13:33:36.000
app1 end 2006-04-03 13:33:36.000
app1 start 2006-04-03 13:33:36.000
app2 start 2006-04-03 13:33:36.000
app2 stuff 2006-04-03 13:33:36.000
app2 end 2006-04-03 13:33:36.000
app2 start 2006-04-03 13:33:36.000
app3 start 2006-04-03 13:33:36.000
app2 end 2006-04-03 13:33:36.000
app2 start 2006-04-03 13:33:36.000
app2 end 2006-04-03 13:33:36.000
app2 start 2006-04-03 13:33:36.000
app2 end 2006-04-03 13:33:36.000
app3 end 2006-04-03 13:33:36.000
app1 end 2006-04-03 13:33:36.000

and another with dr watson crash info
(varchar, datetime)
app1 2006-04-03 13:33:36.000
app2 2006-04-03 13:33:36.000
app1 2006-04-03 13:33:36.000
app1 2006-04-03 13:33:36.000
app3 2006-04-03 13:33:36.000

I'm trying to make a query that will allow
me to see what entries in the first table
occurred wtihin, say, a minute, or maybe 40
seconds of any of the entries in the second
table.

I want all the entries in the second table to
be present, so I know it has to be some sort
of join, probably an outer join.

my syntax is giving me bad results, probably
because I'm just out of practice.

can someone tell me how to put a query together
so I see the data I'm looking for?
Thanks
Jeff

Jeff KishJeff Kish wrote:
> Hi.
> I'm trying but not getting correct results.
(snip)

There are a couple of different ways to do this. This one may not be
the best. It's just the first thing that popped into my mind. Hope it
helps. Your sample data was all the same timestamp. I created sample
data where a crash occurs within one minute of an entry for app1 and
another crash within a minute of an entry for app3. App2 is output in
the results becuase you specifically requested that.

Christopher Secord

create table AppMessage (
App char(4),
MsgType char(5),
MsgDate datetime
)
create table DRWatsonCrash (
App char(4),
CrashDate datetime
)

insert AppMessage values ('app1','start','2006-04-03 13:33:36.000')
insert AppMessage values ('app1','stuff','2006-04-03 13:43:36.000')
insert AppMessage values ('app1','end','2006-04-03 13:53:36.000')
insert AppMessage values ('app2','start','2006-04-04 13:33:36.000')
insert AppMessage values ('app2','stuff','2006-04-05 13:33:36.000')
insert AppMessage values ('app2','end','2006-04-06 13:33:36.000')
insert AppMessage values ('app3','start','2006-04-06 13:43:36.000')
insert AppMessage values ('app3','end','2006-04-06 13:44:36.000')

insert DRWatsonCrash values ('app1','2006-04-03 13:42:56.000')
insert DRWatsonCrash values ('app2','2006-04-03 13:33:36.000')
insert DRWatsonCrash values ('app3','2006-04-06 13:43:56.000')

select AppMessage.App as Application, MsgType, MsgDate
from AppMessage, DrWatsonCrash
where AppMessage.App = DRWatsonCrash.App
and CrashDate between dateadd(minute,-1,MsgDate) and
dateadd(minute,1,MsgDate)
union all
select App as Application, 'DRWatsonCrash', CrashDate as MsgDate
from DRWatsonCrash
order by Application, MsgDate|||Jeff Kish (jeff.kish@.mro.com) writes:
> I have two tables
> one with app, msg, time
> (varchar,datetime,varchar)
> app1 start 2006-04-03 13:33:36.000
> app1 stuff 2006-04-03 13:33:36.000
> app1 end 2006-04-03 13:33:36.000
> and another with dr watson crash info
> (varchar, datetime)
> app1 2006-04-03 13:33:36.000
> app2 2006-04-03 13:33:36.000
> app1 2006-04-03 13:33:36.000
> app1 2006-04-03 13:33:36.000
> app3 2006-04-03 13:33:36.000
>
> I'm trying to make a query that will allow
> me to see what entries in the first table
> occurred wtihin, say, a minute, or maybe 40
> seconds of any of the entries in the second
> table.
> I want all the entries in the second table to
> be present, so I know it has to be some sort
> of join, probably an outer join.

There is a standard recommendation for this sort of posts, and that is
that you post:

o CREATE TABLE statments for your tables.
o INSERT statements with sample data.
o The desired output given the sample.

This makes it very easy to copy and paste into a query tool to develop a
tested solution.

With the information you have given, I can only give a non-tested solution,
which is also is just a guess of what you are looking for.

SELECT w.app1, w.datetimecol, o.event, o.datetimecol
FROM drwatson w
LEFT JOIN othertable o
ON w.app = o.app
AND abs(datediff(ss, w.datetimecol, o.datetime.col)) <= 40

--
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|||On Wed, 5 Apr 2006 21:42:37 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:

>Jeff Kish (jeff.kish@.mro.com) writes:
>> I have two tables
<snip>
>There is a standard recommendation for this sort of posts, and that is
>that you post:
>o CREATE TABLE statments for your tables.
>o INSERT statements with sample data.
>o The desired output given the sample.
>This makes it very easy to copy and paste into a query tool to develop a
>tested solution.
I understand. I'll remember this in the future.
>With the information you have given, I can only give a non-tested solution,
>which is also is just a guess of what you are looking for.
> SELECT w.app1, w.datetimecol, o.event, o.datetimecol
> FROM drwatson w
> LEFT JOIN othertable o
> ON w.app = o.app
> AND abs(datediff(ss, w.datetimecol, o.datetime.col)) <= 40

and also the other message reply said...

>There are a couple of different ways to do this. This one may not be
>the best. It's just the first thing that popped into my mind. Hope it
>helps. Your sample data was all the same timestamp. I created sample
Yes, I was in a hurry and was careless. Normally the data is very
much just as you thought below.
>data where a crash occurs within one minute of an entry for app1 and
>another crash within a minute of an entry for app3. App2 is output in
>the results becuase you specifically requested that.
>Christopher Secord
>create table AppMessage (
>App char(4),
>MsgType char(5),
>MsgDate datetime
>)
>create table DRWatsonCrash (
>App char(4),
>CrashDate datetime
>)
>insert AppMessage values ('app1','start','2006-04-03 13:33:36.000')
>insert AppMessage values ('app1','stuff','2006-04-03 13:43:36.000')
>insert AppMessage values ('app1','end','2006-04-03 13:53:36.000')
>insert AppMessage values ('app2','start','2006-04-04 13:33:36.000')
>insert AppMessage values ('app2','stuff','2006-04-05 13:33:36.000')
>insert AppMessage values ('app2','end','2006-04-06 13:33:36.000')
>insert AppMessage values ('app3','start','2006-04-06 13:43:36.000')
>insert AppMessage values ('app3','end','2006-04-06 13:44:36.000')
>insert DRWatsonCrash values ('app1','2006-04-03 13:42:56.000')
>insert DRWatsonCrash values ('app2','2006-04-03 13:33:36.000')
>insert DRWatsonCrash values ('app3','2006-04-06 13:43:56.000')
>
>select AppMessage.App as Application, MsgType, MsgDate
>from AppMessage, DrWatsonCrash
>where AppMessage.App = DRWatsonCrash.App
>and CrashDate between dateadd(minute,-1,MsgDate) and
>dateadd(minute,1,MsgDate)
>union all
>select App as Application, 'DRWatsonCrash', CrashDate as MsgDate
>from DRWatsonCrash
>order by Application, MsgDate

Thanks much. I'll try both solutions.
I appreciate the feedback.
Jeff

datetime column formula

Hello all:

Using EM to add a column Date_Entered with data type dateTime, what is the syntax to default the date to current date when record is added and to ensure it does not update if the record is modified at some other time in the future. Is it also possible to exclude the time when the column is updated (instead of 4/18/2003 9:32:56 PM the colum would be derived as 4/18/2003)

Is there a publication with listing of all legal suntax used in SQL2000?

Thank youbol has the syntax.

I don't advise using e-m to update the schema.

The sql would be something like

alter table x add dte datetime not null default convert(varchar(8),getdate(),112)

The column is not updated - only defaulted on insert.

If you want it to be set to the current date on update you can do it in a trigger.

Datetimes always include a time - the above will set it to midnight. It is up to you the format in which you display it.

DateTime again - how to us the time aswell as date

Hi All,

I have a SQl Statemnent ... AND dbStartDateTime >= 27/12/2003 19:00:00 ... and get the following error

Incorrect syntax near '19'

I dont understand why it wouldnt like this as this is how it is stored in the database?

I have looked for answers but most people seem to want ot use DateTime without the time

Thanks in advance

LeeYou should enclose the datetime value in single quotes.

 AND dbStartDateTime >= '27/12/2003 19:00:00'

Terri|||Thanks for your help Terri,

I need to re-read the chapter in my book! about syntax etc

Lee

Dateadd and Dynamic SQL

Hi!
I am trying to pass two variables @.date and @.days in the DATEADD
function but getting syntax error message saying "Syntax error
converting the varchar value 'select dateadd(day, ' to a column of data
type int.". I think I am not using the correct syntax. Can you please
help in correcting it?
Thanks,
declare @.date datetime
declare @.days int
set @.date = '5/19/2005'
set @.days = 60
declare @.S varchar(100)
-- hard coded works
--set @.S = 'select dateadd(day, 60,'''+ convert(varchar(10), @.date, 110)
+ ''')'
set @.S = 'select dateadd(day, ' + @.days + ','' + '''+
convert(varchar(10), @.date, 110) + ''')'
print @.S
*** Sent via Developersdex http://www.examnotes.net ***Any reason you are doing dynamic SQL
Anyway the code below works
declare @.date datetime
declare @.days int
set @.date = '5/19/2005'
set @.days = 60
declare @.S varchar(100)
select @.S = dateadd(day, @.days ,convert(varchar(10), @.date, 110) )
print @.S
http://sqlservercode.blogspot.com/
"Test Test" wrote:

> Hi!
> I am trying to pass two variables @.date and @.days in the DATEADD
> function but getting syntax error message saying "Syntax error
> converting the varchar value 'select dateadd(day, ' to a column of data
> type int.". I think I am not using the correct syntax. Can you please
> help in correcting it?
> Thanks,
> declare @.date datetime
> declare @.days int
> set @.date = '5/19/2005'
> set @.days = 60
> declare @.S varchar(100)
> -- hard coded works
> --set @.S = 'select dateadd(day, 60,'''+ convert(varchar(10), @.date, 110)
> + ''')'
> set @.S = 'select dateadd(day, ' + @.days + ','' + '''+
> convert(varchar(10), @.date, 110) + ''')'
> print @.S
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Yes I need it to be done using dynamic SQL bc. Thanks!
*** Sent via Developersdex http://www.examnotes.net ***|||Try,
set @.S = 'select dateadd(day, ' + ltrim(@.days) + ', @.date)'
AMB
"Test Test" wrote:

> Hi!
> I am trying to pass two variables @.date and @.days in the DATEADD
> function but getting syntax error message saying "Syntax error
> converting the varchar value 'select dateadd(day, ' to a column of data
> type int.". I think I am not using the correct syntax. Can you please
> help in correcting it?
> Thanks,
> declare @.date datetime
> declare @.days int
> set @.date = '5/19/2005'
> set @.days = 60
> declare @.S varchar(100)
> -- hard coded works
> --set @.S = 'select dateadd(day, 60,'''+ convert(varchar(10), @.date, 110)
> + ''')'
> set @.S = 'select dateadd(day, ' + @.days + ','' + '''+
> convert(varchar(10), @.date, 110) + ''')'
> print @.S
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Correction,
set @.S = 'select dateadd(day, ' + ltrim(@.days) + ',''' +
convert(varchar(35), @.date, 126) + ''')'
AMB
"Alejandro Mesa" wrote:
> Try,
> set @.S = 'select dateadd(day, ' + ltrim(@.days) + ', @.date)'
>
> AMB
>
> "Test Test" wrote:
>