I have a datetime variable coming from my ASP.NET application that has
a time portion. I give my users the option to perform an equals,
greater than, less than, or between comparison. The trouble comes in
the way the application builds the criteria string. The WHERE clause
passed in is in the format, "(start_dt = '2005/05/16 07:00:00.000')".
What I want to do is only compare the date portion of start_dt to the
date portion of the passed in time. Manipulating the start_dt with the
built-in SQL functions isn't a problem, but altering the date passed in
from the ASP.NET would be a massive framework change in the app.
Is there any way to only compare the date portions of both the SQL
field and the passed in value?
Thanks.Create a stored procedure instead creating the statement dynamically.
create procedure dbo.usp_proc1
@.sd datetime
as
set nocount on
select c1, ..., cn
from table1
where
start_dt >= convert(char(8), @.sd, 112)
and start_dt < convert(char(8), dateadd(day, 1, @.sd), 112)
return @.@.error
go
AMB
"colinhumber" wrote:
> I have a datetime variable coming from my ASP.NET application that has
> a time portion. I give my users the option to perform an equals,
> greater than, less than, or between comparison. The trouble comes in
> the way the application builds the criteria string. The WHERE clause
> passed in is in the format, "(start_dt = '2005/05/16 07:00:00.000')".
> What I want to do is only compare the date portion of start_dt to the
> date portion of the passed in time. Manipulating the start_dt with the
> built-in SQL functions isn't a problem, but altering the date passed in
> from the ASP.NET would be a massive framework change in the app.
> Is there any way to only compare the date portions of both the SQL
> field and the passed in value?
> Thanks.|||You have to convert it to a the valid format you want to comapre it to, e.g.
(from BOL --> Convert)
CONVERT(varchar(8),YourdateinHere,112) which will apply iso date formatting
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"colinhumber" <colinhumber@.discussions.microsoft.com> schrieb im Newsbeitrag
news:AE4EA0BE-51D8-459F-A071-225F6833AFAB@.microsoft.com...
>I have a datetime variable coming from my ASP.NET application that has
> a time portion. I give my users the option to perform an equals,
> greater than, less than, or between comparison. The trouble comes in
> the way the application builds the criteria string. The WHERE clause
> passed in is in the format, "(start_dt = '2005/05/16 07:00:00.000')".
> What I want to do is only compare the date portion of start_dt to the
> date portion of the passed in time. Manipulating the start_dt with the
> built-in SQL functions isn't a problem, but altering the date passed in
> from the ASP.NET would be a massive framework change in the app.
> Is there any way to only compare the date portions of both the SQL
> field and the passed in value?
> Thanks.|||You need to consider using CONVERT fnc with RIGHT
or using DATEPART !
exemple :
right(convert(varchar, @.datetime, 112),10)
or
cast(datepart(hour,@.datetime) as varchar) + ':' +
cast(datepart(minute,@.datetime) as varchar) + ':' +
cast(datepart(second,@.datetime) as varchar)|||Thanks for the quick reply.
Doing the conversion on the start_dt isn't a problem, but as the value being
passed in from the app is in a dynamic string, performing some string
manipulation would be difficult as the string length could vary. The
frameworks as it stands uses dynamic criteria strings so changing that is no
t
an option. I was hoping there was a way to compare only the date portions
without too much manipulation.
"Jens Sü?meyer" wrote:
> You have to convert it to a the valid format you want to comapre it to, e.
g.
> (from BOL --> Convert)
> CONVERT(varchar(8),YourdateinHere,112) which will apply iso date formattin
g
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "colinhumber" <colinhumber@.discussions.microsoft.com> schrieb im Newsbeitr
ag
> news:AE4EA0BE-51D8-459F-A071-225F6833AFAB@.microsoft.com...
>
>|||DateDiff(day, 0, <AnyDate> ) strips off the time portion...
so
Where DateDiff(day, 0, start_dt) <Operator> DateDiff(day, 0, @.PassedInDate)
is one way to do this generically. (Happens to be really fast too.)
"colinhumber" wrote:
> I have a datetime variable coming from my ASP.NET application that has
> a time portion. I give my users the option to perform an equals,
> greater than, less than, or between comparison. The trouble comes in
> the way the application builds the criteria string. The WHERE clause
> passed in is in the format, "(start_dt = '2005/05/16 07:00:00.000')".
> What I want to do is only compare the date portion of start_dt to the
> date portion of the passed in time. Manipulating the start_dt with the
> built-in SQL functions isn't a problem, but altering the date passed in
> from the ASP.NET would be a massive framework change in the app.
> Is there any way to only compare the date portions of both the SQL
> field and the passed in value?
> Thanks.
Showing posts with label comparison. Show all posts
Showing posts with label comparison. Show all posts
Sunday, March 25, 2012
Friday, February 17, 2012
Date Comparison Question
I have 9 datetime columns in a DataRow.
In a SELECT statement, I would like to have a Boolean return True if Date1
is later than every one of date2 thru date9, otherwise return False. Some
dates may be Null.
Is there a concise way to accomplish this?
TIA
Jav--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"Jav" wrote:
> I have 9 datetime columns in a DataRow.
> In a SELECT statement, I would like to have a Boolean return True if Date1
> is later than every one of date2 thru date9, otherwise return False. Som
e
> dates may be Null.
> Is there a concise way to accomplish this?
> TIA
> Jav|||I don't think there is. If you need to do a lot of stuff with columns
then you can use dynamic SQL, but for just that check I think it's
going to be a very large case statement (perhaps use a udf to trim this
down). Is this required just by chance, or should you be looking at the
normalisation of your data?
when you start getting lots of columns like "expiryDate1, expiryDate2,
... expiryDate9" you might want to consider having a table Expirydates.
Cheers
Will|||A CASE statement is probably your best bet. It probably will not be very
concise, but it will work.
Keith Kratochvil
"Jav" <Jav@.discussions.microsoft.com> wrote in message
news:B8B9C1C7-165A-454D-ACEB-25CC24386979@.microsoft.com...
>I have 9 datetime columns in a DataRow.
> In a SELECT statement, I would like to have a Boolean return True if Date1
> is later than every one of date2 thru date9, otherwise return False.
> Some
> dates may be Null.
> Is there a concise way to accomplish this?
> TIA
> Jav|||Thanks Will, Keith,
These are dates of certain events concerning a single entity represented by
the DataRow. The dates change frequently as those events occur in the life
of the entity, some dates changing more frequently than others. There are
tens of thousands of entities in the Table.
This is the only time I have needed to do the comparison. Would separating
the dates into their own table offer any performance benefit over a long Cas
e
statement? Since I use Guids as PK, it seems counterintuitive to separate a
n
8 byte field into a 24 plus byte DataRow and add 9 rows where there was one,
notwithstanding the normalization issues.
Thanks once again, I appreciate your help.
Jav
"Will" wrote:
> I don't think there is. If you need to do a lot of stuff with columns
> then you can use dynamic SQL, but for just that check I think it's
> going to be a very large case statement (perhaps use a udf to trim this
> down). Is this required just by chance, or should you be looking at the
> normalisation of your data?
> when you start getting lots of columns like "expiryDate1, expiryDate2,
> ... expiryDate9" you might want to consider having a table Expirydates.
> Cheers
> Will
>|||SQL doesn't have True or False, but you can adjust as required.
SELECT CASE WHEN date1 IS NULL THEN 'F'
WHEN date2 >= date1 THEN 'F'
WHEN date3 >= date1 THEN 'F'
WHEN date4 >= date1 THEN 'F'
WHEN date5 >= date1 THEN 'F'
WHEN date6 >= date1 THEN 'F'
WHEN date7 >= date1 THEN 'F'
WHEN date8 >= date1 THEN 'F'
WHEN date9 >= date1 THEN 'F'
ELSE 'T'
END
FROM Whatever
Roy Harvey
Beacon Falls, CT
On Wed, 5 Apr 2006 08:44:01 -0700, Jav <Jav@.discussions.microsoft.com>
wrote:
>I have 9 datetime columns in a DataRow.
>In a SELECT statement, I would like to have a Boolean return True if Date1
>is later than every one of date2 thru date9, otherwise return False. Some
>dates may be Null.
>Is there a concise way to accomplish this?
>TIA
>Jav|||Thanks Roy,
I was just using True-False symbolically. I would return 1 or 0. Your CASE
statement example is very helpful. Thanks.
Jav
"Roy Harvey" wrote:
> SQL doesn't have True or False, but you can adjust as required.
> SELECT CASE WHEN date1 IS NULL THEN 'F'
> WHEN date2 >= date1 THEN 'F'
> WHEN date3 >= date1 THEN 'F'
> WHEN date4 >= date1 THEN 'F'
> WHEN date5 >= date1 THEN 'F'
> WHEN date6 >= date1 THEN 'F'
> WHEN date7 >= date1 THEN 'F'
> WHEN date8 >= date1 THEN 'F'
> WHEN date9 >= date1 THEN 'F'
> ELSE 'T'
> END
> FROM Whatever
> Roy Harvey
> Beacon Falls, CT
> On Wed, 5 Apr 2006 08:44:01 -0700, Jav <Jav@.discussions.microsoft.com>
> wrote:
>
>|||Performance of you database may be affected by normalising your data,
as you would then have to join on it. However performance is only one
aspect of a database, and scalability and maintainability are not to be
ignored either.
As to how it will affect the performance, one advantage of separating
it out into a separate table is that you can then index that one
column, providing a much faster search on your comparison. Also (and
this is just my own experience) I've noticed tables with a lot of
columns taking a lot longer to select, especially if you're often not
using all 9 cells, and just putting nulls in.
The best thing would be to try it out and see how it affects things
Cheers
Will|||Jav wrote:
> Would separating
> the dates into their own table offer any performance benefit over a long C
ase
> statement? Since I use Guids as PK, it seems counterintuitive to separate
an
> 8 byte field into a 24 plus byte DataRow and add 9 rows where there was on
e,
> notwithstanding the normalization issues.
This is the wrong question and is not the best way to approach design
problems. Start with a fully normalized design. Only compromise that if
you are forced to.
I'm not saying your design is or is not well normalized because I don't
know your data, but a large set of dates as columns in a table
intuitively looks like a weak design.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
In a SELECT statement, I would like to have a Boolean return True if Date1
is later than every one of date2 thru date9, otherwise return False. Some
dates may be Null.
Is there a concise way to accomplish this?
TIA
Jav--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"Jav" wrote:
> I have 9 datetime columns in a DataRow.
> In a SELECT statement, I would like to have a Boolean return True if Date1
> is later than every one of date2 thru date9, otherwise return False. Som
e
> dates may be Null.
> Is there a concise way to accomplish this?
> TIA
> Jav|||I don't think there is. If you need to do a lot of stuff with columns
then you can use dynamic SQL, but for just that check I think it's
going to be a very large case statement (perhaps use a udf to trim this
down). Is this required just by chance, or should you be looking at the
normalisation of your data?
when you start getting lots of columns like "expiryDate1, expiryDate2,
... expiryDate9" you might want to consider having a table Expirydates.
Cheers
Will|||A CASE statement is probably your best bet. It probably will not be very
concise, but it will work.
Keith Kratochvil
"Jav" <Jav@.discussions.microsoft.com> wrote in message
news:B8B9C1C7-165A-454D-ACEB-25CC24386979@.microsoft.com...
>I have 9 datetime columns in a DataRow.
> In a SELECT statement, I would like to have a Boolean return True if Date1
> is later than every one of date2 thru date9, otherwise return False.
> Some
> dates may be Null.
> Is there a concise way to accomplish this?
> TIA
> Jav|||Thanks Will, Keith,
These are dates of certain events concerning a single entity represented by
the DataRow. The dates change frequently as those events occur in the life
of the entity, some dates changing more frequently than others. There are
tens of thousands of entities in the Table.
This is the only time I have needed to do the comparison. Would separating
the dates into their own table offer any performance benefit over a long Cas
e
statement? Since I use Guids as PK, it seems counterintuitive to separate a
n
8 byte field into a 24 plus byte DataRow and add 9 rows where there was one,
notwithstanding the normalization issues.
Thanks once again, I appreciate your help.
Jav
"Will" wrote:
> I don't think there is. If you need to do a lot of stuff with columns
> then you can use dynamic SQL, but for just that check I think it's
> going to be a very large case statement (perhaps use a udf to trim this
> down). Is this required just by chance, or should you be looking at the
> normalisation of your data?
> when you start getting lots of columns like "expiryDate1, expiryDate2,
> ... expiryDate9" you might want to consider having a table Expirydates.
> Cheers
> Will
>|||SQL doesn't have True or False, but you can adjust as required.
SELECT CASE WHEN date1 IS NULL THEN 'F'
WHEN date2 >= date1 THEN 'F'
WHEN date3 >= date1 THEN 'F'
WHEN date4 >= date1 THEN 'F'
WHEN date5 >= date1 THEN 'F'
WHEN date6 >= date1 THEN 'F'
WHEN date7 >= date1 THEN 'F'
WHEN date8 >= date1 THEN 'F'
WHEN date9 >= date1 THEN 'F'
ELSE 'T'
END
FROM Whatever
Roy Harvey
Beacon Falls, CT
On Wed, 5 Apr 2006 08:44:01 -0700, Jav <Jav@.discussions.microsoft.com>
wrote:
>I have 9 datetime columns in a DataRow.
>In a SELECT statement, I would like to have a Boolean return True if Date1
>is later than every one of date2 thru date9, otherwise return False. Some
>dates may be Null.
>Is there a concise way to accomplish this?
>TIA
>Jav|||Thanks Roy,
I was just using True-False symbolically. I would return 1 or 0. Your CASE
statement example is very helpful. Thanks.
Jav
"Roy Harvey" wrote:
> SQL doesn't have True or False, but you can adjust as required.
> SELECT CASE WHEN date1 IS NULL THEN 'F'
> WHEN date2 >= date1 THEN 'F'
> WHEN date3 >= date1 THEN 'F'
> WHEN date4 >= date1 THEN 'F'
> WHEN date5 >= date1 THEN 'F'
> WHEN date6 >= date1 THEN 'F'
> WHEN date7 >= date1 THEN 'F'
> WHEN date8 >= date1 THEN 'F'
> WHEN date9 >= date1 THEN 'F'
> ELSE 'T'
> END
> FROM Whatever
> Roy Harvey
> Beacon Falls, CT
> On Wed, 5 Apr 2006 08:44:01 -0700, Jav <Jav@.discussions.microsoft.com>
> wrote:
>
>|||Performance of you database may be affected by normalising your data,
as you would then have to join on it. However performance is only one
aspect of a database, and scalability and maintainability are not to be
ignored either.
As to how it will affect the performance, one advantage of separating
it out into a separate table is that you can then index that one
column, providing a much faster search on your comparison. Also (and
this is just my own experience) I've noticed tables with a lot of
columns taking a lot longer to select, especially if you're often not
using all 9 cells, and just putting nulls in.
The best thing would be to try it out and see how it affects things
Cheers
Will|||Jav wrote:
> Would separating
> the dates into their own table offer any performance benefit over a long C
ase
> statement? Since I use Guids as PK, it seems counterintuitive to separate
an
> 8 byte field into a 24 plus byte DataRow and add 9 rows where there was on
e,
> notwithstanding the normalization issues.
This is the wrong question and is not the best way to approach design
problems. Start with a fully normalized design. Only compromise that if
you are forced to.
I'm not saying your design is or is not well normalized because I don't
know your data, but a large set of dates as columns in a table
intuitively looks like a weak design.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Date comparison problem
Hi,
If for some reason you can't get the back-end logic to work correctly,
at least try to get it to work almost-correctly (e.g. add/subtract one
day using the DateDiff function) and then use VBS to finish the job.|||On Mon, 25 Sep 2006 17:49:33 +0100, noone wrote:
Hi ukwebguy,
>
Hi ukwebguy,
>
First, read the information in Tibor's "ultimate guide to the datetime
datatype": http://www.karaszi.com/SQLServer/info_datetime.asp.
>
If the DisplayFrom and DisplayUntil columns contain a date only (ie, the
time part is set to the default value of midnight), you could use this:
>
DECLARE @.Today smalldatetime
-- Strip time portion from current date and time
SET @.Today = DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
SELECT *
FROM news
WHERE DisplayUntil >= @.Today
AND DisplayFrom <= @.Today
>
If DisplayFrom and DisplayUntil contain time portions as well, then you
need to clarify your requirements. Here's a syntax that MIGHT work for
you, but only if my guesswork about your actual requirements is correct.
>
DECLARE @.Today smalldatetime
-- Strip time portion from current date and time
SET @.Today = DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
SELECT *
FROM news
WHERE DisplayUntil >= @.Today
AND DisplayFrom < @.Today + 1
>
>
--
Hugo Kornelis, SQL Server MVP
I am designing an application which displays news topics until midnight
on the DisplayUntil date and then they should drop out. Unfortunately,
they seem to be dropping out at mid-day.
I'm storing the 'DisplayFrom' and 'DisplayUntil' dates as SmallDateTime
fields so the date is in the format "25/09/2006 00:0:00" and I'm
comparing them with GetDate() which (I suspect) includes the actual time
as well.
I'm using the following SQL..
SELECT *
FROM t_news
WHERE n_DisplayUntil >= GetDate() AND n_DisplayFrom <= GetDate()
in MS SQL Server Express 2005
I think this is probably all too simplistic - is there a way to compare
these dates so that they display from the beginning of the start date
(ie 00:00:01) until the end of the last day (ie 23:59:59)?
I'm getting so desparate that I'm even considering pulling the whole
recordset and doing the comparison in VBScript - and that's desparate!
Can anyone help me, please?
Regards
ukwebguynoone wrote:
Quote:
Originally Posted by
I'm getting so desparate that I'm even considering pulling the whole
recordset and doing the comparison in VBScript - and that's desparate!
If for some reason you can't get the back-end logic to work correctly,
at least try to get it to work almost-correctly (e.g. add/subtract one
day using the DateDiff function) and then use VBS to finish the job.|||On Mon, 25 Sep 2006 17:49:33 +0100, noone wrote:
Quote:
Originally Posted by
>Hi,
>
>I am designing an application which displays news topics until midnight
>on the DisplayUntil date and then they should drop out. Unfortunately,
>they seem to be dropping out at mid-day.
>
>I'm storing the 'DisplayFrom' and 'DisplayUntil' dates as SmallDateTime
>fields so the date is in the format "25/09/2006 00:0:00" and I'm
>comparing them with GetDate() which (I suspect) includes the actual time
>as well.
>
>I'm using the following SQL..
>
>SELECT *
>FROM t_news
>WHERE n_DisplayUntil >= GetDate() AND n_DisplayFrom <= GetDate()
>
>in MS SQL Server Express 2005
>
>I think this is probably all too simplistic - is there a way to compare
>these dates so that they display from the beginning of the start date
>(ie 00:00:01) until the end of the last day (ie 23:59:59)?
>
>I'm getting so desparate that I'm even considering pulling the whole
>recordset and doing the comparison in VBScript - and that's desparate!
>Can anyone help me, please?
>
>Regards
>ukwebguy
Hi ukwebguy,
First, read the information in Tibor's "ultimate guide to the datetime
datatype": http://www.karaszi.com/SQLServer/info_datetime.asp.
If the DisplayFrom and DisplayUntil columns contain a date only (ie, the
time part is set to the default value of midnight), you could use this:
DECLARE @.Today smalldatetime
-- Strip time portion from current date and time
SET @.Today = DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
SELECT *
FROM news
WHERE DisplayUntil >= @.Today
AND DisplayFrom <= @.Today
If DisplayFrom and DisplayUntil contain time portions as well, then you
need to clarify your requirements. Here's a syntax that MIGHT work for
you, but only if my guesswork about your actual requirements is correct.
DECLARE @.Today smalldatetime
-- Strip time portion from current date and time
SET @.Today = DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
SELECT *
FROM news
WHERE DisplayUntil >= @.Today
AND DisplayFrom < @.Today + 1
--
Hugo Kornelis, SQL Server MVP|||http://sql-server-performance.com/fk_datetime.asp
Madhivanan
Hugo Kornelis wrote:
Quote:
Originally Posted by
On Mon, 25 Sep 2006 17:49:33 +0100, noone wrote:
>
Quote:
Originally Posted by
Hi,
I am designing an application which displays news topics until midnight
on the DisplayUntil date and then they should drop out. Unfortunately,
they seem to be dropping out at mid-day.
I'm storing the 'DisplayFrom' and 'DisplayUntil' dates as SmallDateTime
fields so the date is in the format "25/09/2006 00:0:00" and I'm
comparing them with GetDate() which (I suspect) includes the actual time
as well.
I'm using the following SQL..
SELECT *
FROM t_news
WHERE n_DisplayUntil >= GetDate() AND n_DisplayFrom <= GetDate()
in MS SQL Server Express 2005
I think this is probably all too simplistic - is there a way to compare
these dates so that they display from the beginning of the start date
(ie 00:00:01) until the end of the last day (ie 23:59:59)?
I'm getting so desparate that I'm even considering pulling the whole
recordset and doing the comparison in VBScript - and that's desparate!
Can anyone help me, please?
Regards
ukwebguy
>
Hi ukwebguy,
>
First, read the information in Tibor's "ultimate guide to the datetime
datatype": http://www.karaszi.com/SQLServer/info_datetime.asp.
>
If the DisplayFrom and DisplayUntil columns contain a date only (ie, the
time part is set to the default value of midnight), you could use this:
>
DECLARE @.Today smalldatetime
-- Strip time portion from current date and time
SET @.Today = DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
SELECT *
FROM news
WHERE DisplayUntil >= @.Today
AND DisplayFrom <= @.Today
>
If DisplayFrom and DisplayUntil contain time portions as well, then you
need to clarify your requirements. Here's a syntax that MIGHT work for
you, but only if my guesswork about your actual requirements is correct.
>
DECLARE @.Today smalldatetime
-- Strip time portion from current date and time
SET @.Today = DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
SELECT *
FROM news
WHERE DisplayUntil >= @.Today
AND DisplayFrom < @.Today + 1
>
>
--
Hugo Kornelis, SQL Server MVP
Date comparison problem
Hi,
I am designing an application which displays news topics until midnight
on the DisplayUntil date and then they should drop out. Unfortunately,
they seem to be dropping out at mid-day.
I'm storing the 'DisplayFrom' and 'DisplayUntil' dates as SmallDateTime
fields so the date is in the format "25/09/2006 00:0:00" and I'm
comparing them with GetDate() which (I suspect) includes the actual time
as well.
I'm using the following SQL..
SELECT *
FROM t_news
WHERE n_DisplayUntil >= GetDate() AND n_DisplayFrom <= GetDate()
in MS SQL Server Express 2005
I think this is probably all too simplistic - is there a way to compare
these dates so that they display from the beginning of the start date
(ie 00:00:01) until the end of the last day (ie 23:59:59)?
I'm getting so desparate that I'm even considering pulling the whole
recordset and doing the comparison in VBScript - and that's desparate!
Can anyone help me, please?
Regards
ukwebguyHi,
what about something like this here:
DECLARE @.Start DATETIME
DECLARE @.End DATETIME
SET @.Start = '20060924'
SET @.End = '20060924'
Select 1
Where @.Start <= GETDATE() AND DATEDIFF(dd,GETDATE(),@.END) >= 0
HTH, jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--
Date comparison problem
Hi
I have a table that contains information that has start dates and end
dates. They are stored in short date format.
I have built a web page that initially returned all the information. I
then want to return information spcific to todays date, where I used
dim todDate
todDate = now
then I did
shTodDate = FormatDateTime(todDate, 2)
which returned the date to a web page that was in the same format as
what was in the access table.
SELECT * from Events WHERE stDate = # ' & shTodDate & ' #;"
This was fine beacuse it returned all events that started on that day.
This doesn't help though because some events last a few days, so I
tried the following
SELECT * FROM Events WHERE stDate >=#'&shTodDate&'# AND
endDate<=#'&shTodDate&'#;
This still only returned events that started on the current day.
Does anyone know how I can use <= or >= when comparing dates?
Thanks
puREp3s+puREp3s+ (colin42@.btinternet.com) writes:
> SELECT * FROM Events WHERE stDate >=#'&shTodDate&'# AND
> endDate<=#'&shTodDate&'#;
> This still only returned events that started on the current day.
> Does anyone know how I can use <= or >= when comparing dates?
Maybe the folks over in comp.datatabases.ms-access knows? It seems
that you are using Access, from the syntax. At least it is not MS
SQL Server.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||It looks like you have your comparison operators backwards. In TSQL syntax,
this is what you would do to get any events in progress at this moment:
DECLARE @.t DATETIME
SET @.t = CURRENT_TIMESTAMP
SELECT *
FROM Events
WHERE stDate <= @.t AND endDate >= @.t;
... or, if the end date may be NULL, then:
SELECT *
FROM Events
WHERE stDate <= @.t AND (endDate >= @.t OR endDate IS NULL);
Hope that helps,
Rich
"puREp3s+" <colin42@.btinternet.com> wrote in message
news:7a6f18a3.0310211330.7ad2a1d0@.posting.google.c om...
> Hi
> I have a table that contains information that has start dates and end
> dates. They are stored in short date format.
> I have built a web page that initially returned all the information. I
> then want to return information spcific to todays date, where I used
> dim todDate
> todDate = now
> then I did
> shTodDate = FormatDateTime(todDate, 2)
> which returned the date to a web page that was in the same format as
> what was in the access table.
> SELECT * from Events WHERE stDate = # ' & shTodDate & ' #;"
> This was fine beacuse it returned all events that started on that day.
> This doesn't help though because some events last a few days, so I
> tried the following
> SELECT * FROM Events WHERE stDate >=#'&shTodDate&'# AND
> endDate<=#'&shTodDate&'#;
> This still only returned events that started on the current day.
> Does anyone know how I can use <= or >= when comparing dates?
> Thanks
> puREp3s+
Date comparison logic in a trigger without hard-coding the year
I'm having trouble trying to figure out how to write logic into a trigger
that will find when a date in the Tax Due Date field, falls in a particular
quarter and was created after a set day of the year without hard-coding the
year information. I'm trying to avoid having to update the trigger.
Scenario: An extract of data for 1st Quarter tax payments is always sent to
the agent on November 7th of the previous year, 2nd Quarter on February 15th
of the same year, 3rd Quarter on May 15th of the same year, and 4th Quarter
on August 15th of the same year. (The extract is always sent out before the
quarter starts to allow for processing/payment before the tax is actually
due.) The Tax Due Date is auto-generated according to different country
laws. I want to send an email to the user whenever they generate a Tax Due
Date that falls into a particular quarter for which the data extract has
already been sent out. Basically if today's date = December 15, 2006 or
January 4, 2007 and a Tax Due Date is generated = February 3, 2007, send an
email because December 15th or January 4th is after November 7th. This woul
d
apply until the day before the same quarter extract is sent out for the
following year, which in this example would be November 6th, 2007. The
extracts are always sent on the same day of the year. Any help would be
greatly appreciated!
--
Finn GirlUse a calendar table.
http://www.aspfaq.com/2519
"FinnGirl" <FinnGirl@.discussions.microsoft.com> wrote in message
news:E888E3D6-EB97-4DBA-B73E-19C6EAA12633@.microsoft.com...
> I'm having trouble trying to figure out how to write logic into a trigger
> that will find when a date in the Tax Due Date field, falls in a
> particular
> quarter and was created after a set day of the year without hard-coding
> the
> year information. I'm trying to avoid having to update the trigger.
> Scenario: An extract of data for 1st Quarter tax payments is always sent
> to
> the agent on November 7th of the previous year, 2nd Quarter on February
> 15th
> of the same year, 3rd Quarter on May 15th of the same year, and 4th
> Quarter
> on August 15th of the same year. (The extract is always sent out before
> the
> quarter starts to allow for processing/payment before the tax is actually
> due.) The Tax Due Date is auto-generated according to different country
> laws. I want to send an email to the user whenever they generate a Tax
> Due
> Date that falls into a particular quarter for which the data extract has
> already been sent out. Basically if today's date = December 15, 2006 or
> January 4, 2007 and a Tax Due Date is generated = February 3, 2007, send
> an
> email because December 15th or January 4th is after November 7th. This
> would
> apply until the day before the same quarter extract is sent out for the
> following year, which in this example would be November 6th, 2007. The
> extracts are always sent on the same day of the year. Any help would be
> greatly appreciated!
> --
> Finn Girl
that will find when a date in the Tax Due Date field, falls in a particular
quarter and was created after a set day of the year without hard-coding the
year information. I'm trying to avoid having to update the trigger.
Scenario: An extract of data for 1st Quarter tax payments is always sent to
the agent on November 7th of the previous year, 2nd Quarter on February 15th
of the same year, 3rd Quarter on May 15th of the same year, and 4th Quarter
on August 15th of the same year. (The extract is always sent out before the
quarter starts to allow for processing/payment before the tax is actually
due.) The Tax Due Date is auto-generated according to different country
laws. I want to send an email to the user whenever they generate a Tax Due
Date that falls into a particular quarter for which the data extract has
already been sent out. Basically if today's date = December 15, 2006 or
January 4, 2007 and a Tax Due Date is generated = February 3, 2007, send an
email because December 15th or January 4th is after November 7th. This woul
d
apply until the day before the same quarter extract is sent out for the
following year, which in this example would be November 6th, 2007. The
extracts are always sent on the same day of the year. Any help would be
greatly appreciated!
--
Finn GirlUse a calendar table.
http://www.aspfaq.com/2519
"FinnGirl" <FinnGirl@.discussions.microsoft.com> wrote in message
news:E888E3D6-EB97-4DBA-B73E-19C6EAA12633@.microsoft.com...
> I'm having trouble trying to figure out how to write logic into a trigger
> that will find when a date in the Tax Due Date field, falls in a
> particular
> quarter and was created after a set day of the year without hard-coding
> the
> year information. I'm trying to avoid having to update the trigger.
> Scenario: An extract of data for 1st Quarter tax payments is always sent
> to
> the agent on November 7th of the previous year, 2nd Quarter on February
> 15th
> of the same year, 3rd Quarter on May 15th of the same year, and 4th
> Quarter
> on August 15th of the same year. (The extract is always sent out before
> the
> quarter starts to allow for processing/payment before the tax is actually
> due.) The Tax Due Date is auto-generated according to different country
> laws. I want to send an email to the user whenever they generate a Tax
> Due
> Date that falls into a particular quarter for which the data extract has
> already been sent out. Basically if today's date = December 15, 2006 or
> January 4, 2007 and a Tax Due Date is generated = February 3, 2007, send
> an
> email because December 15th or January 4th is after November 7th. This
> would
> apply until the day before the same quarter extract is sent out for the
> following year, which in this example would be November 6th, 2007. The
> extracts are always sent on the same day of the year. Any help would be
> greatly appreciated!
> --
> Finn Girl
Date Comparison Issue
Hi all,
I am trying to create a stored procedure that will check a date field.
I want to check for records that are equal to or greater then 90 days
from the current date. I am trying to check this against a field
called LastUpdate. Is there an easy way in SQL to do this?
TIA"Icarus" <christopher@.NOSPAMreardenweb.com> wrote in message
news:dfekrvgejb2bg5gt4bqfdjj2p33j1gsj6v@.4ax.com...
> Hi all,
> I am trying to create a stored procedure that will check a date field.
> I want to check for records that are equal to or greater then 90 days
> from the current date. I am trying to check this against a field
> called LastUpdate. Is there an easy way in SQL to do this?
> TIA
SELECT *
FROM T
WHERE LastUpdate <= CURRENT_TIMESTAMP - 90
Regards,
jag|||...
WHERE lastupdate >=
DATEADD(DAY,-90,CONVERT(CHAR(8),CURRENT_TIMESTAMP,112))
--
David Portas
----
Please reply only to the newsgroup
--
"Icarus" <christopher@.NOSPAMreardenweb.com> wrote in message
news:dfekrvgejb2bg5gt4bqfdjj2p33j1gsj6v@.4ax.com...
> Hi all,
> I am trying to create a stored procedure that will check a date field.
> I want to check for records that are equal to or greater then 90 days
> from the current date. I am trying to check this against a field
> called LastUpdate. Is there an easy way in SQL to do this?
> TIA|||Thanks! Worked perfectly.
On Tue, 18 Nov 2003 15:48:07 GMT, "John Gilson" <jag@.acm.org> wrote:
>"Icarus" <christopher@.NOSPAMreardenweb.com> wrote in message
>news:dfekrvgejb2bg5gt4bqfdjj2p33j1gsj6v@.4ax.com...
>> Hi all,
>>
>> I am trying to create a stored procedure that will check a date field.
>> I want to check for records that are equal to or greater then 90 days
>> from the current date. I am trying to check this against a field
>> called LastUpdate. Is there an easy way in SQL to do this?
>>
>> TIA
>SELECT *
>FROM T
>WHERE LastUpdate <= CURRENT_TIMESTAMP - 90
>Regards,
>jag
Date Comparison in Stored Procedure
Hello,
I want to compare 2 datetime but only on Date part, excluding Time .
For example
Date 1 = 07/01/2004 14:00:00
Date 2 = 07/01/2004 16:00:00
I want have true for Date 1 = Date 2
How can I do ?
Excuse my bad EnglishYou will have to use CONVERT with appropriate style that strips the time part and compare like:
if convert(char(8), @.date1, 112) = convert(char(8), @.date1, 112)
The style 112 is the ISO unseparated string format YYYYMMDD. You can do the same against a column in a table also. If the date column has an index then you can use a search condition like below to make efficient use of it since using CONVERT directly will not use the index.
-- restrict to all time values within a day
where datecol >= convert(char(8), @.date, 112)
and datecol < convert(char(8), @.date + 1, 112)|||
just cast as int
example:
declare @.date1 datetime
declare @.date2 datetime
set @.date1 = getdate()
set @.date2 = dateadd(mi, 1, @.date1)
select @.date1 [Date 1], @.date2 [Date 2],
case @.date1
when @.date2 then 'Yes!!!'
else 'No!!!'
end [Same DateTime?],
case cast(@.date1 as int)
when cast(@.date2 as int) then 'Yes!!!'
else 'No!!!'
end [Same Date?]
I could not understand the below code given by you in your post...
where datecol >= convert(char(8), @.date, 112)
and datecol < convert(char(8), @.date + 1, 112)will this not give a conversion error, if datecol is a datetime column ?
Anand
Date comparison in MDX
i have a question concerning MDX:
i have the following mdx statement:
with set [Last18month] as strtoset('{[Dim Time].[hYearMonthQuarterRS].[Year_Name].&[Calendar 2005].&[Quarter 4, 2005].&[2005-12-01T00:00:00].lag(17):[Dim Time].[hYearMonthQuarterRS].[Year_Name].&[Calendar 2005].&[Quarter 4, 2005].&[2005-12-01T00:00:00]}')
select
{(([Policy].[Next Status].&
(([Policy].[Next Status].&[R] ), [Measures].[PolicyCount])} on columns,
[Last18month] on rows
from [PolicySales];
and i want to add a filter according [Policy].[Next Status Date] is within the selected month,
for example if the selected month is [Dim Time].[hYearMonthQuarterRS].[Year_Name].&[Calendar 2005].&[Quarter 4, 2005].&[2005-12-01T00:00:00],i want to filter according to [Policy].[Next Status Date] is between [2005-12-01T00:00:00] and
[2005-12-31T00:00:00]
Can you please help me
Regards
Assuming that the .MemberValue for both [Next Status Date] and Month attributes are defined as DateTime values (eg: [Dim Time].[hYearMonthQuarterRS].[Year_Name].&[Calendar 2005].&[Quarter 4, 2005].&[2005-12-01T00:00:00].MemberValue is 2005-12-01T00:00:00), a "filtered" [PolicyCount] could be created, like:
with
set [Last18month] as strtoset('{[Dim Time].[hYearMonthQuarterRS].[Year_Name].&[Calendar 2005].&[Quarter 4, 2005].&[2005-12-01T00:00:00].lag(17):[Dim Time].[hYearMonthQuarterRS].[Year_Name].&[Calendar 2005].&[Quarter 4, 2005].&[2005-12-01T00:00:00]}')
member [Measures].[NextStatusCount] as
Aggregate(Filter([Policy].[Next Status Date].[Next Status Date],
[Policy].[Next Status Date].MemberValue >= [Dim Time].[hYearMonthQuarterRS].MemberValue and
[Policy].[Next Status Date].MemberValue < [Dim Time].[hYearMonthQuarterRS].NextMember.MemberValue),
[Measures].[PolicyCount])
select
{(([Policy].[Next Status].& ), [Measures].[NextStatusCount]),
(([Policy].[Next Status].&[R] ), [Measures].[NextStatusCount])} on columns,
[Last18month] on rows
from [PolicySales]
Dear sir,
thank you very much for your reply,your answer works fine
it's very helpfull,but my real problem is: the selected month used in the filter is a parameter in Reporting services,and the parameter could be:
[Dim Time].[hYearMonthQuarterRS].[Year_Name].&[Calendar 2005]
or
[Dim Time].[hYearMonthQuarterRS].[Year_Name].&[Calendar 2005].&[Quarter 4, 2005]
or
[Dim Time].[hYearMonthQuarterRS].[Year_Name].&[Calendar 2005].&[Quarter 4, 2005].&[2005-12-01T00:00:00]
so how i can replace the value of the parameter selected in ([Dim Time].[hYearMonthQuarterRS].MemberValue) and ( [Dim Time].[hYearMonthQuarterRS].NextMember.MemberValue)
the parameter name is: DimTimehYearMonthQuarterRS
as i told before the parameter can take any value from the hierarchy [Dim Time].[hYearMonthQuarterRS]
this is my mdx used in the reporting services that i want to add the filter to it:
="WITH member nextstatus_E as '(([Policy].[Next Status].&
Thank you
|||If I understand your query correctly, regardless of which level the parameter is at, the query should report on the [Last18month] from the end. For example, for 2005 selection, it should cover Jul, 2004 to Dec, 2005. So the filter in my query will always be evaluated at the month level for each of 18 rows - unless the filter should use the same range (Jan, 2005 to Dec, 2005) for each of the 18 rows, regardless of the month? If you could give a simple example of a parameter selection, and how the report should get filtered, that would help clarify.Date Comparison excluding time
I have a stored procedure that is suppose to get all records that have an expiration date of today. My where clause is as follows:
Round2ExpDate <= getdate()
This is not working because of the time factore. What is the best way to truncate the time for each date? All recommendations are greatly appreciated.You can remove time by casting a datetime datatype as a varchar.
date comparison and filtering
Im creating a stored proc where i need to filter an Orders table
results based on a date field as following:
The column i'm filtering is called DeliveryDate
How to I filter DeliveryDate for This W
, This Month.
This W:
Do I have to calculate the start of w and end of w and then use
a between #start# and #end#? How is this done.
This Month:
Do I have to calculate the start of month and end of month and then use
a between #start# and #end#
Does SQL provide specialized date functions for this?
Thank YouAre youo sure you mean "filter" A Filter is something that block some
records and lets some records through, based on a predicate.
If you actually want to filter the records, you use a Where Clause
Select * From TableName
Where DeliveryDate >= <StartDate> And DeliveryDate <EndDate>
To filter for only thos month, you can use the following expressions:
Foor the StartDate:
Convert(VarChar(6), getDate(), 112) + '01'
And for endDate, use:
Convert(VarChar(6), DateAdd(month, 1, getDate(), 112) + '01'
To filter for the current w, you have to decide what day of the w you
want to use... And set a Server-wide setting called DateFirst. Set it to a
value = 1 for Monday, 2 for Tuesday, etc.
Set DateFirst 1 -- (to make Monday the first day of the w)
And then use these expressions:
StartDate:
convert(varChar(8), dateadd(day, 1-datepart(dw, getdate()),
getdate()), 112),
and EndDate:
convert(varChar(8), Dateadd(day, 8-datepart(dw, getdate()),
getdate()), 112)
"Opa" wrote:
> Im creating a stored proc where i need to filter an Orders table
> results based on a date field as following:
> The column i'm filtering is called DeliveryDate
> How to I filter DeliveryDate for This W, This Month.
> This W:
> Do I have to calculate the start of w and end of w and then use
> a between #start# and #end#? How is this done.
> This Month:
> Do I have to calculate the start of month and end of month and then use
> a between #start# and #end#
> Does SQL provide specialized date functions for this?
> Thank You
>
>
>
>
>|||Thanks a lot.
I will try your suggestions.
"CBretana" wrote:
> Are youo sure you mean "filter" A Filter is something that block some
> records and lets some records through, based on a predicate.
> If you actually want to filter the records, you use a Where Clause
> Select * From TableName
> Where DeliveryDate >= <StartDate> And DeliveryDate <EndDate>
> To filter for only thos month, you can use the following expressions:
> Foor the StartDate:
> Convert(VarChar(6), getDate(), 112) + '01'
> And for endDate, use:
> Convert(VarChar(6), DateAdd(month, 1, getDate(), 112) + '01'
>
> To filter for the current w, you have to decide what day of the w yo
u
> want to use... And set a Server-wide setting called DateFirst. Set it to
a
> value = 1 for Monday, 2 for Tuesday, etc.
> Set DateFirst 1 -- (to make Monday the first day of the w)
> And then use these expressions:
> StartDate:
> convert(varChar(8), dateadd(day, 1-datepart(dw, getdate()),
> getdate()), 112),
> and EndDate:
> convert(varChar(8), Dateadd(day, 8-datepart(dw, getdate()),
> getdate()), 112)
> "Opa" wrote:
>|||Another way you can do this
for this month
where Datepart(mm,deliverydate) = datepart(mm,getdate())
for this w
where datepart(wk,deliverydate) = datepart(wk,getdate())
performance may not be the best when you use functions of any type in the
where clause
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Opa" <Opa@.discussions.microsoft.com> wrote in message
news:98449239-878D-41E4-8DC6-57CB5C43AF6D@.microsoft.com...
> Im creating a stored proc where i need to filter an Orders table
> results based on a date field as following:
> The column i'm filtering is called DeliveryDate
> How to I filter DeliveryDate for This W, This Month.
> This W:
> Do I have to calculate the start of w and end of w and then use
> a between #start# and #end#? How is this done.
> This Month:
> Do I have to calculate the start of month and end of month and then use
> a between #start# and #end#
> Does SQL provide specialized date functions for this?
> Thank You
>
>
>
>
>
results based on a date field as following:
The column i'm filtering is called DeliveryDate
How to I filter DeliveryDate for This W
, This Month.This W
:Do I have to calculate the start of w
and end of w and then usea between #start# and #end#? How is this done.
This Month:
Do I have to calculate the start of month and end of month and then use
a between #start# and #end#
Does SQL provide specialized date functions for this?
Thank YouAre youo sure you mean "filter" A Filter is something that block some
records and lets some records through, based on a predicate.
If you actually want to filter the records, you use a Where Clause
Select * From TableName
Where DeliveryDate >= <StartDate> And DeliveryDate <EndDate>
To filter for only thos month, you can use the following expressions:
Foor the StartDate:
Convert(VarChar(6), getDate(), 112) + '01'
And for endDate, use:
Convert(VarChar(6), DateAdd(month, 1, getDate(), 112) + '01'
To filter for the current w
, you have to decide what day of the w youwant to use... And set a Server-wide setting called DateFirst. Set it to a
value = 1 for Monday, 2 for Tuesday, etc.
Set DateFirst 1 -- (to make Monday the first day of the w
)And then use these expressions:
StartDate:
convert(varChar(8), dateadd(day, 1-datepart(dw, getdate()),
getdate()), 112),
and EndDate:
convert(varChar(8), Dateadd(day, 8-datepart(dw, getdate()),
getdate()), 112)
"Opa" wrote:
> Im creating a stored proc where i need to filter an Orders table
> results based on a date field as following:
> The column i'm filtering is called DeliveryDate
> How to I filter DeliveryDate for This W
, This Month.> This W
:> Do I have to calculate the start of w
and end of w and then use> a between #start# and #end#? How is this done.
> This Month:
> Do I have to calculate the start of month and end of month and then use
> a between #start# and #end#
> Does SQL provide specialized date functions for this?
> Thank You
>
>
>
>
>|||Thanks a lot.
I will try your suggestions.
"CBretana" wrote:
> Are youo sure you mean "filter" A Filter is something that block some
> records and lets some records through, based on a predicate.
> If you actually want to filter the records, you use a Where Clause
> Select * From TableName
> Where DeliveryDate >= <StartDate> And DeliveryDate <EndDate>
> To filter for only thos month, you can use the following expressions:
> Foor the StartDate:
> Convert(VarChar(6), getDate(), 112) + '01'
> And for endDate, use:
> Convert(VarChar(6), DateAdd(month, 1, getDate(), 112) + '01'
>
> To filter for the current w
, you have to decide what day of the w you
> want to use... And set a Server-wide setting called DateFirst. Set it to
a
> value = 1 for Monday, 2 for Tuesday, etc.
> Set DateFirst 1 -- (to make Monday the first day of the w
)> And then use these expressions:
> StartDate:
> convert(varChar(8), dateadd(day, 1-datepart(dw, getdate()),
> getdate()), 112),
> and EndDate:
> convert(varChar(8), Dateadd(day, 8-datepart(dw, getdate()),
> getdate()), 112)
> "Opa" wrote:
>|||Another way you can do this
for this month
where Datepart(mm,deliverydate) = datepart(mm,getdate())
for this w
where datepart(wk,deliverydate) = datepart(wk,getdate())
performance may not be the best when you use functions of any type in the
where clause
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Opa" <Opa@.discussions.microsoft.com> wrote in message
news:98449239-878D-41E4-8DC6-57CB5C43AF6D@.microsoft.com...
> Im creating a stored proc where i need to filter an Orders table
> results based on a date field as following:
> The column i'm filtering is called DeliveryDate
> How to I filter DeliveryDate for This W
, This Month.> This W
:> Do I have to calculate the start of w
and end of w and then use> a between #start# and #end#? How is this done.
> This Month:
> Do I have to calculate the start of month and end of month and then use
> a between #start# and #end#
> Does SQL provide specialized date functions for this?
> Thank You
>
>
>
>
>
date comparison
I'm trying to filter rows to a datetime column as it matches to a datetime
column in another table. For example:
For Each irow In dsbnlsum.Tables(0).Select("inv_dt between '" &
(irowd("inv_dt") - 1) & Chr(39) & " and '" & (irowd("inv_dt") + 1) &
Chr(39))
(Developed in vb .net, but a basic question for sql server as well.)
Note that I took a datetime column in a table represented by irowd and
overlapped it, so that my datetime column (inv_dt) would be inside the
range. I'm not even sure this will work, but is there a better way? FYI -
inv_dt is often created as Now, so it could be 8/22/2002 5:16pm or
03/18/2001 12:02am, etc.
Tx for any help.
Bernie YaegerSearch SQL books on line for 'Date and Time Functions' also SQL uses BETWEEN
if you wish to search for a date with a range. eg:-
Select * from table where DateColumn between '20031007 13:27' AND GetDate()
HTH
Ryan Waight, MCDBA, MCSE
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:DZygb.60405$E95.31061722@.news4.srv.hcvlny.cv.net...
> I'm trying to filter rows to a datetime column as it matches to a datetime
> column in another table. For example:
> For Each irow In dsbnlsum.Tables(0).Select("inv_dt between '" &
> (irowd("inv_dt") - 1) & Chr(39) & " and '" & (irowd("inv_dt") + 1) &
> Chr(39))
> (Developed in vb .net, but a basic question for sql server as well.)
> Note that I took a datetime column in a table represented by irowd and
> overlapped it, so that my datetime column (inv_dt) would be inside the
> range. I'm not even sure this will work, but is there a better way?
FYI -
> inv_dt is often created as Now, so it could be 8/22/2002 5:16pm or
> 03/18/2001 12:02am, etc.
> Tx for any help.
> Bernie Yaeger
>
column in another table. For example:
For Each irow In dsbnlsum.Tables(0).Select("inv_dt between '" &
(irowd("inv_dt") - 1) & Chr(39) & " and '" & (irowd("inv_dt") + 1) &
Chr(39))
(Developed in vb .net, but a basic question for sql server as well.)
Note that I took a datetime column in a table represented by irowd and
overlapped it, so that my datetime column (inv_dt) would be inside the
range. I'm not even sure this will work, but is there a better way? FYI -
inv_dt is often created as Now, so it could be 8/22/2002 5:16pm or
03/18/2001 12:02am, etc.
Tx for any help.
Bernie YaegerSearch SQL books on line for 'Date and Time Functions' also SQL uses BETWEEN
if you wish to search for a date with a range. eg:-
Select * from table where DateColumn between '20031007 13:27' AND GetDate()
HTH
Ryan Waight, MCDBA, MCSE
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:DZygb.60405$E95.31061722@.news4.srv.hcvlny.cv.net...
> I'm trying to filter rows to a datetime column as it matches to a datetime
> column in another table. For example:
> For Each irow In dsbnlsum.Tables(0).Select("inv_dt between '" &
> (irowd("inv_dt") - 1) & Chr(39) & " and '" & (irowd("inv_dt") + 1) &
> Chr(39))
> (Developed in vb .net, but a basic question for sql server as well.)
> Note that I took a datetime column in a table represented by irowd and
> overlapped it, so that my datetime column (inv_dt) would be inside the
> range. I'm not even sure this will work, but is there a better way?
FYI -
> inv_dt is often created as Now, so it could be 8/22/2002 5:16pm or
> 03/18/2001 12:02am, etc.
> Tx for any help.
> Bernie Yaeger
>
date comparison
Is there a way to compare to a date variable with a 'flattened' date? What
I mean is, something like this:
select * from paysched where sdate = '02/24/2003'
now sdate is a datetime column. It is '02/24/2003 00:00:00' and so it
works. But what if it were '02/24/2003' 09:22:02'? I could do a 'between',
I know, but is there any way to 'flatten' the sdate in the select so that it
only contains the date, not the time, element?
Tx for any help.
Bernie YaegerWhile you can do something like that, you don't want to. The reason is the
any attempt to manipulate the value of the column in the where clause will
cause the optimizer to ignore any potentially useful indexes on that column.
Your best bet is to use between.
Less efficiently:
where convert(char(8), sdate, 112) = '20030224' -- note - string to string
comparison
where cast(convert(char(8), sdate, 112) as datetime) = '20030224' -- note -
string constant promoted to datetime
where month(sdate) = 2 and day(sdate) = 24 and year(sdate) = 2003
I'm sure there are others. BTW - you should format your strings as yyyymmdd
if you want them to be convertable to datetime without worrying about
regional or language or dateformat settings.
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:YAVlb.16908$F8.3240116@.news4.srv.hcvlny.cv.net...
> Is there a way to compare to a date variable with a 'flattened' date?
What
> I mean is, something like this:
> select * from paysched where sdate = '02/24/2003'
> now sdate is a datetime column. It is '02/24/2003 00:00:00' and so it
> works. But what if it were '02/24/2003' 09:22:02'? I could do a
'between',
> I know, but is there any way to 'flatten' the sdate in the select so that
it
> only contains the date, not the time, element?
> Tx for any help.
> Bernie Yaeger
>|||Hi Scott,
Tx for the advice. I had actually been working with convert to get it done
while awaiting a response. Re the optimizer - this is a very small table
(maybe 150 rows) so it is not significant here, but I appreciate your
recommendation.
Tx again,
Bernie
"Scott Morris" <bogus@.bogus.com> wrote in message
news:OjyNr6ZmDHA.3288@.tk2msftngp13.phx.gbl...
> While you can do something like that, you don't want to. The reason is
the
> any attempt to manipulate the value of the column in the where clause will
> cause the optimizer to ignore any potentially useful indexes on that
column.
> Your best bet is to use between.
> Less efficiently:
> where convert(char(8), sdate, 112) = '20030224' -- note - string to string
> comparison
> where cast(convert(char(8), sdate, 112) as datetime) = '20030224' --
note -
> string constant promoted to datetime
> where month(sdate) = 2 and day(sdate) = 24 and year(sdate) = 2003
> I'm sure there are others. BTW - you should format your strings as
yyyymmdd
> if you want them to be convertable to datetime without worrying about
> regional or language or dateformat settings.
> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> news:YAVlb.16908$F8.3240116@.news4.srv.hcvlny.cv.net...
> > Is there a way to compare to a date variable with a 'flattened' date?
> What
> > I mean is, something like this:
> >
> > select * from paysched where sdate = '02/24/2003'
> >
> > now sdate is a datetime column. It is '02/24/2003 00:00:00' and so it
> > works. But what if it were '02/24/2003' 09:22:02'? I could do a
> 'between',
> > I know, but is there any way to 'flatten' the sdate in the select so
that
> it
> > only contains the date, not the time, element?
> >
> > Tx for any help.
> >
> > Bernie Yaeger
> >
> >
>|||Scott,
FYI, in all your three examples you do not have the datetime column "alone" in the WHERE clause.
This will mean that SQL Server cannot use an index for that search argument. My favorite is:
DECLARE @.TheDate
SET @.TheDate = '20030523'
WHERE dtcol >= @.TheDate AND dtcol < DATEDADD(day, 1, @.TheDay)
Note that the column is not involved in any type of calculation.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Scott Morris" <bogus@.bogus.com> wrote in message news:OjyNr6ZmDHA.3288@.tk2msftngp13.phx.gbl...
> While you can do something like that, you don't want to. The reason is the
> any attempt to manipulate the value of the column in the where clause will
> cause the optimizer to ignore any potentially useful indexes on that column.
> Your best bet is to use between.
> Less efficiently:
> where convert(char(8), sdate, 112) = '20030224' -- note - string to string
> comparison
> where cast(convert(char(8), sdate, 112) as datetime) = '20030224' -- note -
> string constant promoted to datetime
> where month(sdate) = 2 and day(sdate) = 24 and year(sdate) = 2003
> I'm sure there are others. BTW - you should format your strings as yyyymmdd
> if you want them to be convertable to datetime without worrying about
> regional or language or dateformat settings.
> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> news:YAVlb.16908$F8.3240116@.news4.srv.hcvlny.cv.net...
> > Is there a way to compare to a date variable with a 'flattened' date?
> What
> > I mean is, something like this:
> >
> > select * from paysched where sdate = '02/24/2003'
> >
> > now sdate is a datetime column. It is '02/24/2003 00:00:00' and so it
> > works. But what if it were '02/24/2003' 09:22:02'? I could do a
> 'between',
> > I know, but is there any way to 'flatten' the sdate in the select so that
> it
> > only contains the date, not the time, element?
> >
> > Tx for any help.
> >
> > Bernie Yaeger
> >
> >
>|||Exactly - I was giving the less efficient examples. The recommendation was
to use between (same as >= and <=).
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:Oc$4YlfmDHA.3312@.tk2msftngp13.phx.gbl...
> Scott,
> FYI, in all your three examples you do not have the datetime column
"alone" in the WHERE clause.
> This will mean that SQL Server cannot use an index for that search
argument. My favorite is:
> DECLARE @.TheDate
> SET @.TheDate = '20030523'
> WHERE dtcol >= @.TheDate AND dtcol < DATEDADD(day, 1, @.TheDay)
> Note that the column is not involved in any type of calculation.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Scott Morris" <bogus@.bogus.com> wrote in message
news:OjyNr6ZmDHA.3288@.tk2msftngp13.phx.gbl...
> > While you can do something like that, you don't want to. The reason is
the
> > any attempt to manipulate the value of the column in the where clause
will
> > cause the optimizer to ignore any potentially useful indexes on that
column.
> > Your best bet is to use between.
> >
> > Less efficiently:
> > where convert(char(8), sdate, 112) = '20030224' -- note - string to
string
> > comparison
> > where cast(convert(char(8), sdate, 112) as datetime) = '20030224' --
note -
> > string constant promoted to datetime
> > where month(sdate) = 2 and day(sdate) = 24 and year(sdate) = 2003
> >
> > I'm sure there are others. BTW - you should format your strings as
yyyymmdd
> > if you want them to be convertable to datetime without worrying about
> > regional or language or dateformat settings.
> >
> > "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> > news:YAVlb.16908$F8.3240116@.news4.srv.hcvlny.cv.net...
> > > Is there a way to compare to a date variable with a 'flattened' date?
> > What
> > > I mean is, something like this:
> > >
> > > select * from paysched where sdate = '02/24/2003'
> > >
> > > now sdate is a datetime column. It is '02/24/2003 00:00:00' and so it
> > > works. But what if it were '02/24/2003' 09:22:02'? I could do a
> > 'between',
> > > I know, but is there any way to 'flatten' the sdate in the select so
that
> > it
> > > only contains the date, not the time, element?
> > >
> > > Tx for any help.
> > >
> > > Bernie Yaeger
> > >
> > >
> >
> >
>|||left off a bit - between is the same ... assuming the appropriate boundaries
are used.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%2375zDtimDHA.3256@.tk2msftngp13.phx.gbl...
> Exactly - I was giving the less efficient examples. The recommendation
was
> to use between (same as >= and <=).
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:Oc$4YlfmDHA.3312@.tk2msftngp13.phx.gbl...
> > Scott,
> >
> > FYI, in all your three examples you do not have the datetime column
> "alone" in the WHERE clause.
> > This will mean that SQL Server cannot use an index for that search
> argument. My favorite is:
> >
> > DECLARE @.TheDate
> > SET @.TheDate = '20030523'
> > WHERE dtcol >= @.TheDate AND dtcol < DATEDADD(day, 1, @.TheDay)
> >
> > Note that the column is not involved in any type of calculation.
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Scott Morris" <bogus@.bogus.com> wrote in message
> news:OjyNr6ZmDHA.3288@.tk2msftngp13.phx.gbl...
> > > While you can do something like that, you don't want to. The reason
is
> the
> > > any attempt to manipulate the value of the column in the where clause
> will
> > > cause the optimizer to ignore any potentially useful indexes on that
> column.
> > > Your best bet is to use between.
> > >
> > > Less efficiently:
> > > where convert(char(8), sdate, 112) = '20030224' -- note - string to
> string
> > > comparison
> > > where cast(convert(char(8), sdate, 112) as datetime) = '20030224' --
> note -
> > > string constant promoted to datetime
> > > where month(sdate) = 2 and day(sdate) = 24 and year(sdate) = 2003
> > >
> > > I'm sure there are others. BTW - you should format your strings as
> yyyymmdd
> > > if you want them to be convertable to datetime without worrying about
> > > regional or language or dateformat settings.
> > >
> > > "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> > > news:YAVlb.16908$F8.3240116@.news4.srv.hcvlny.cv.net...
> > > > Is there a way to compare to a date variable with a 'flattened'
date?
> > > What
> > > > I mean is, something like this:
> > > >
> > > > select * from paysched where sdate = '02/24/2003'
> > > >
> > > > now sdate is a datetime column. It is '02/24/2003 00:00:00' and so
it
> > > > works. But what if it were '02/24/2003' 09:22:02'? I could do a
> > > 'between',
> > > > I know, but is there any way to 'flatten' the sdate in the select so
> that
> > > it
> > > > only contains the date, not the time, element?
> > > >
> > > > Tx for any help.
> > > >
> > > > Bernie Yaeger
> > > >
> > > >
> > >
> > >
> >
> >
>
I mean is, something like this:
select * from paysched where sdate = '02/24/2003'
now sdate is a datetime column. It is '02/24/2003 00:00:00' and so it
works. But what if it were '02/24/2003' 09:22:02'? I could do a 'between',
I know, but is there any way to 'flatten' the sdate in the select so that it
only contains the date, not the time, element?
Tx for any help.
Bernie YaegerWhile you can do something like that, you don't want to. The reason is the
any attempt to manipulate the value of the column in the where clause will
cause the optimizer to ignore any potentially useful indexes on that column.
Your best bet is to use between.
Less efficiently:
where convert(char(8), sdate, 112) = '20030224' -- note - string to string
comparison
where cast(convert(char(8), sdate, 112) as datetime) = '20030224' -- note -
string constant promoted to datetime
where month(sdate) = 2 and day(sdate) = 24 and year(sdate) = 2003
I'm sure there are others. BTW - you should format your strings as yyyymmdd
if you want them to be convertable to datetime without worrying about
regional or language or dateformat settings.
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:YAVlb.16908$F8.3240116@.news4.srv.hcvlny.cv.net...
> Is there a way to compare to a date variable with a 'flattened' date?
What
> I mean is, something like this:
> select * from paysched where sdate = '02/24/2003'
> now sdate is a datetime column. It is '02/24/2003 00:00:00' and so it
> works. But what if it were '02/24/2003' 09:22:02'? I could do a
'between',
> I know, but is there any way to 'flatten' the sdate in the select so that
it
> only contains the date, not the time, element?
> Tx for any help.
> Bernie Yaeger
>|||Hi Scott,
Tx for the advice. I had actually been working with convert to get it done
while awaiting a response. Re the optimizer - this is a very small table
(maybe 150 rows) so it is not significant here, but I appreciate your
recommendation.
Tx again,
Bernie
"Scott Morris" <bogus@.bogus.com> wrote in message
news:OjyNr6ZmDHA.3288@.tk2msftngp13.phx.gbl...
> While you can do something like that, you don't want to. The reason is
the
> any attempt to manipulate the value of the column in the where clause will
> cause the optimizer to ignore any potentially useful indexes on that
column.
> Your best bet is to use between.
> Less efficiently:
> where convert(char(8), sdate, 112) = '20030224' -- note - string to string
> comparison
> where cast(convert(char(8), sdate, 112) as datetime) = '20030224' --
note -
> string constant promoted to datetime
> where month(sdate) = 2 and day(sdate) = 24 and year(sdate) = 2003
> I'm sure there are others. BTW - you should format your strings as
yyyymmdd
> if you want them to be convertable to datetime without worrying about
> regional or language or dateformat settings.
> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> news:YAVlb.16908$F8.3240116@.news4.srv.hcvlny.cv.net...
> > Is there a way to compare to a date variable with a 'flattened' date?
> What
> > I mean is, something like this:
> >
> > select * from paysched where sdate = '02/24/2003'
> >
> > now sdate is a datetime column. It is '02/24/2003 00:00:00' and so it
> > works. But what if it were '02/24/2003' 09:22:02'? I could do a
> 'between',
> > I know, but is there any way to 'flatten' the sdate in the select so
that
> it
> > only contains the date, not the time, element?
> >
> > Tx for any help.
> >
> > Bernie Yaeger
> >
> >
>|||Scott,
FYI, in all your three examples you do not have the datetime column "alone" in the WHERE clause.
This will mean that SQL Server cannot use an index for that search argument. My favorite is:
DECLARE @.TheDate
SET @.TheDate = '20030523'
WHERE dtcol >= @.TheDate AND dtcol < DATEDADD(day, 1, @.TheDay)
Note that the column is not involved in any type of calculation.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Scott Morris" <bogus@.bogus.com> wrote in message news:OjyNr6ZmDHA.3288@.tk2msftngp13.phx.gbl...
> While you can do something like that, you don't want to. The reason is the
> any attempt to manipulate the value of the column in the where clause will
> cause the optimizer to ignore any potentially useful indexes on that column.
> Your best bet is to use between.
> Less efficiently:
> where convert(char(8), sdate, 112) = '20030224' -- note - string to string
> comparison
> where cast(convert(char(8), sdate, 112) as datetime) = '20030224' -- note -
> string constant promoted to datetime
> where month(sdate) = 2 and day(sdate) = 24 and year(sdate) = 2003
> I'm sure there are others. BTW - you should format your strings as yyyymmdd
> if you want them to be convertable to datetime without worrying about
> regional or language or dateformat settings.
> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> news:YAVlb.16908$F8.3240116@.news4.srv.hcvlny.cv.net...
> > Is there a way to compare to a date variable with a 'flattened' date?
> What
> > I mean is, something like this:
> >
> > select * from paysched where sdate = '02/24/2003'
> >
> > now sdate is a datetime column. It is '02/24/2003 00:00:00' and so it
> > works. But what if it were '02/24/2003' 09:22:02'? I could do a
> 'between',
> > I know, but is there any way to 'flatten' the sdate in the select so that
> it
> > only contains the date, not the time, element?
> >
> > Tx for any help.
> >
> > Bernie Yaeger
> >
> >
>|||Exactly - I was giving the less efficient examples. The recommendation was
to use between (same as >= and <=).
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:Oc$4YlfmDHA.3312@.tk2msftngp13.phx.gbl...
> Scott,
> FYI, in all your three examples you do not have the datetime column
"alone" in the WHERE clause.
> This will mean that SQL Server cannot use an index for that search
argument. My favorite is:
> DECLARE @.TheDate
> SET @.TheDate = '20030523'
> WHERE dtcol >= @.TheDate AND dtcol < DATEDADD(day, 1, @.TheDay)
> Note that the column is not involved in any type of calculation.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Scott Morris" <bogus@.bogus.com> wrote in message
news:OjyNr6ZmDHA.3288@.tk2msftngp13.phx.gbl...
> > While you can do something like that, you don't want to. The reason is
the
> > any attempt to manipulate the value of the column in the where clause
will
> > cause the optimizer to ignore any potentially useful indexes on that
column.
> > Your best bet is to use between.
> >
> > Less efficiently:
> > where convert(char(8), sdate, 112) = '20030224' -- note - string to
string
> > comparison
> > where cast(convert(char(8), sdate, 112) as datetime) = '20030224' --
note -
> > string constant promoted to datetime
> > where month(sdate) = 2 and day(sdate) = 24 and year(sdate) = 2003
> >
> > I'm sure there are others. BTW - you should format your strings as
yyyymmdd
> > if you want them to be convertable to datetime without worrying about
> > regional or language or dateformat settings.
> >
> > "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> > news:YAVlb.16908$F8.3240116@.news4.srv.hcvlny.cv.net...
> > > Is there a way to compare to a date variable with a 'flattened' date?
> > What
> > > I mean is, something like this:
> > >
> > > select * from paysched where sdate = '02/24/2003'
> > >
> > > now sdate is a datetime column. It is '02/24/2003 00:00:00' and so it
> > > works. But what if it were '02/24/2003' 09:22:02'? I could do a
> > 'between',
> > > I know, but is there any way to 'flatten' the sdate in the select so
that
> > it
> > > only contains the date, not the time, element?
> > >
> > > Tx for any help.
> > >
> > > Bernie Yaeger
> > >
> > >
> >
> >
>|||left off a bit - between is the same ... assuming the appropriate boundaries
are used.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%2375zDtimDHA.3256@.tk2msftngp13.phx.gbl...
> Exactly - I was giving the less efficient examples. The recommendation
was
> to use between (same as >= and <=).
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:Oc$4YlfmDHA.3312@.tk2msftngp13.phx.gbl...
> > Scott,
> >
> > FYI, in all your three examples you do not have the datetime column
> "alone" in the WHERE clause.
> > This will mean that SQL Server cannot use an index for that search
> argument. My favorite is:
> >
> > DECLARE @.TheDate
> > SET @.TheDate = '20030523'
> > WHERE dtcol >= @.TheDate AND dtcol < DATEDADD(day, 1, @.TheDay)
> >
> > Note that the column is not involved in any type of calculation.
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Scott Morris" <bogus@.bogus.com> wrote in message
> news:OjyNr6ZmDHA.3288@.tk2msftngp13.phx.gbl...
> > > While you can do something like that, you don't want to. The reason
is
> the
> > > any attempt to manipulate the value of the column in the where clause
> will
> > > cause the optimizer to ignore any potentially useful indexes on that
> column.
> > > Your best bet is to use between.
> > >
> > > Less efficiently:
> > > where convert(char(8), sdate, 112) = '20030224' -- note - string to
> string
> > > comparison
> > > where cast(convert(char(8), sdate, 112) as datetime) = '20030224' --
> note -
> > > string constant promoted to datetime
> > > where month(sdate) = 2 and day(sdate) = 24 and year(sdate) = 2003
> > >
> > > I'm sure there are others. BTW - you should format your strings as
> yyyymmdd
> > > if you want them to be convertable to datetime without worrying about
> > > regional or language or dateformat settings.
> > >
> > > "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> > > news:YAVlb.16908$F8.3240116@.news4.srv.hcvlny.cv.net...
> > > > Is there a way to compare to a date variable with a 'flattened'
date?
> > > What
> > > > I mean is, something like this:
> > > >
> > > > select * from paysched where sdate = '02/24/2003'
> > > >
> > > > now sdate is a datetime column. It is '02/24/2003 00:00:00' and so
it
> > > > works. But what if it were '02/24/2003' 09:22:02'? I could do a
> > > 'between',
> > > > I know, but is there any way to 'flatten' the sdate in the select so
> that
> > > it
> > > > only contains the date, not the time, element?
> > > >
> > > > Tx for any help.
> > > >
> > > > Bernie Yaeger
> > > >
> > > >
> > >
> > >
> >
> >
>
Date Comparison
I have two tables that I am needing to link by the tables date field. In one table the date field is defined as varchar(23). The time for this field is always zeros.
Example: '2005-12-27 00:00:00.000'
The other table is defined as datetime, and it does have the date and time in this field.
Example: 2005-12-27 08:00:35.000
The problem i am having is
2005-12-27 00:00:00.000 does not = 2005-12-27 08:00:35.000.
Because I will never have more than one record on the same date I would like to be able to only compare the date. Example 2005-12-27 = 2005-12-27
Since the fields are 2 different field types, this is giving me a problem. Could someone please help. I have tried everything I know to do.
What I really need is the a way to format the datetime fields date into a string such as '2005-12-27'.
Hopefully what i have written makes sense.
Thanksselect cast(convert(varchar(11),getdate()) as Datetime)
this eliminates the time stamp and sets it to 2006-05-27 00:00:00.000
This will allow you to join
Suggestion make your varchar field a date field.|||Or,Use the below code to convert date to varchar and compare with ur varchar field
select convert(varchar,DATEADD(dd, DATEDIFF(dd,0,getdate()), 0),121)|||That worked perfect! Thanks!
Example: '2005-12-27 00:00:00.000'
The other table is defined as datetime, and it does have the date and time in this field.
Example: 2005-12-27 08:00:35.000
The problem i am having is
2005-12-27 00:00:00.000 does not = 2005-12-27 08:00:35.000.
Because I will never have more than one record on the same date I would like to be able to only compare the date. Example 2005-12-27 = 2005-12-27
Since the fields are 2 different field types, this is giving me a problem. Could someone please help. I have tried everything I know to do.
What I really need is the a way to format the datetime fields date into a string such as '2005-12-27'.
Hopefully what i have written makes sense.
Thanksselect cast(convert(varchar(11),getdate()) as Datetime)
this eliminates the time stamp and sets it to 2006-05-27 00:00:00.000
This will allow you to join
Suggestion make your varchar field a date field.|||Or,Use the below code to convert date to varchar and compare with ur varchar field
select convert(varchar,DATEADD(dd, DATEDIFF(dd,0,getdate()), 0),121)|||That worked perfect! Thanks!
date comparison
Hi there!
I'm having some problems with a query considering it's performance. I
want to make a view of saleslines (table 1) joined with a purchline
(table 2). The purchline should show the last purchamount of an item,
having the latest date before the salesdate.
When running the query I can go get a cup of coffee on the other side
of the world... Any suggestions how to make this query run faster?
At this moment I have made something like this:
SELECT
dbo.SALESLINE.SALESID,
dbo.SALESLINE.ITEMID,
dbo.SALESLINE.QTYORDERED,
dbo.SALESLINE.LINEAMOUNT,
dbo.SALESLINE.CREATEDDATE AS datumVerkoop,
dbo.purchLINE.CREATEDDATE AS datumInkoop,
FROM
dbo.PURCHLINE INNER JOIN
dbo.SALESLINE ON dbo.PURCHLINE.ITEMID = dbo.SALESLINE.ITEMID
WHERE
dbo.PURCHLINE.RECID =
(SELECT
MAX(dbo.purchline.recid)
FROM
dbo.purchline WITH (nolock)
WHERE
(dbo.PURCHLINE.QTYORDERED <> 0) AND
dbo.purchline.createddate <= dbo.salesline.createddate AND
dbo.purchline.itemid = dbo.salesline.itemid)Can you POST DDL and insert scripts for sample data.
"Q" wrote:
> Hi there!
> I'm having some problems with a query considering it's performance. I
> want to make a view of saleslines (table 1) joined with a purchline
> (table 2). The purchline should show the last purchamount of an item,
> having the latest date before the salesdate.
> When running the query I can go get a cup of coffee on the other side
> of the world... Any suggestions how to make this query run faster?
> At this moment I have made something like this:
> SELECT
> dbo.SALESLINE.SALESID,
> dbo.SALESLINE.ITEMID,
> dbo.SALESLINE.QTYORDERED,
> dbo.SALESLINE.LINEAMOUNT,
> dbo.SALESLINE.CREATEDDATE AS datumVerkoop,
> dbo.purchLINE.CREATEDDATE AS datumInkoop,
> FROM
> dbo.PURCHLINE INNER JOIN
> dbo.SALESLINE ON dbo.PURCHLINE.ITEMID = dbo.SALESLINE.ITEMID
> WHERE
> dbo.PURCHLINE.RECID =
> (SELECT
> MAX(dbo.purchline.recid)
> FROM
> dbo.purchline WITH (nolock)
> WHERE
> (dbo.PURCHLINE.QTYORDERED <> 0) AND
> dbo.purchline.createddate <= dbo.salesline.createddate AND
> dbo.purchline.itemid = dbo.salesline.itemid)
>|||I did not create the tables myself. You probably want to know something
about datatypes and indexes I suppose...
salesid => varchar 20
itemid => varchar 30
qtyordered => numeric 13(28,12)
lineamount => numeric 13(28,12)
createddate => datetime 8
The length of the datatypes are mostly larger as needed, but are not
allowed to be changed.
All used columns is the purchline are indexed, and the columns salesid,
itemid are indexed.|||Can you try this... Not sure how much improvement this will give !!.
SELECT
dbo.SALESLINE.SALESID,
dbo.SALESLINE.ITEMID,
dbo.SALESLINE.QTYORDERED,
dbo.SALESLINE.LINEAMOUNT,
dbo.SALESLINE.CREATEDDATE AS datumVerkoop,
dbo.ph.CREATEDDATE AS datumInkoop,
FROM
dbo.SALESLINE
INNER JOIN
(
select
sl.salesid ,MAX(pl.recid) as LatestPurchLineId
from
salesline sl
inner join purchline pl on
sl.itemid = pl.itemid and
sl.createddate > pl.createdon and
pl.qtyordered <> 0
group by
sl.SalesId
) plat ON
dbo.salesline.salesid = plat.salesid
INNER JOIN PurchLine pl ON
pl.recid = plat.LatestPurchLineId
- Sha Anand
"Q" wrote:
> I did not create the tables myself. You probably want to know something
> about datatypes and indexes I suppose...
> salesid => varchar 20
> itemid => varchar 30
> qtyordered => numeric 13(28,12)
> lineamount => numeric 13(28,12)
> createddate => datetime 8
> The length of the datatypes are mostly larger as needed, but are not
> allowed to be changed.
> All used columns is the purchline are indexed, and the columns salesid,
> itemid are indexed.
>
I'm having some problems with a query considering it's performance. I
want to make a view of saleslines (table 1) joined with a purchline
(table 2). The purchline should show the last purchamount of an item,
having the latest date before the salesdate.
When running the query I can go get a cup of coffee on the other side
of the world... Any suggestions how to make this query run faster?
At this moment I have made something like this:
SELECT
dbo.SALESLINE.SALESID,
dbo.SALESLINE.ITEMID,
dbo.SALESLINE.QTYORDERED,
dbo.SALESLINE.LINEAMOUNT,
dbo.SALESLINE.CREATEDDATE AS datumVerkoop,
dbo.purchLINE.CREATEDDATE AS datumInkoop,
FROM
dbo.PURCHLINE INNER JOIN
dbo.SALESLINE ON dbo.PURCHLINE.ITEMID = dbo.SALESLINE.ITEMID
WHERE
dbo.PURCHLINE.RECID =
(SELECT
MAX(dbo.purchline.recid)
FROM
dbo.purchline WITH (nolock)
WHERE
(dbo.PURCHLINE.QTYORDERED <> 0) AND
dbo.purchline.createddate <= dbo.salesline.createddate AND
dbo.purchline.itemid = dbo.salesline.itemid)Can you POST DDL and insert scripts for sample data.
"Q" wrote:
> Hi there!
> I'm having some problems with a query considering it's performance. I
> want to make a view of saleslines (table 1) joined with a purchline
> (table 2). The purchline should show the last purchamount of an item,
> having the latest date before the salesdate.
> When running the query I can go get a cup of coffee on the other side
> of the world... Any suggestions how to make this query run faster?
> At this moment I have made something like this:
> SELECT
> dbo.SALESLINE.SALESID,
> dbo.SALESLINE.ITEMID,
> dbo.SALESLINE.QTYORDERED,
> dbo.SALESLINE.LINEAMOUNT,
> dbo.SALESLINE.CREATEDDATE AS datumVerkoop,
> dbo.purchLINE.CREATEDDATE AS datumInkoop,
> FROM
> dbo.PURCHLINE INNER JOIN
> dbo.SALESLINE ON dbo.PURCHLINE.ITEMID = dbo.SALESLINE.ITEMID
> WHERE
> dbo.PURCHLINE.RECID =
> (SELECT
> MAX(dbo.purchline.recid)
> FROM
> dbo.purchline WITH (nolock)
> WHERE
> (dbo.PURCHLINE.QTYORDERED <> 0) AND
> dbo.purchline.createddate <= dbo.salesline.createddate AND
> dbo.purchline.itemid = dbo.salesline.itemid)
>|||I did not create the tables myself. You probably want to know something
about datatypes and indexes I suppose...
salesid => varchar 20
itemid => varchar 30
qtyordered => numeric 13(28,12)
lineamount => numeric 13(28,12)
createddate => datetime 8
The length of the datatypes are mostly larger as needed, but are not
allowed to be changed.
All used columns is the purchline are indexed, and the columns salesid,
itemid are indexed.|||Can you try this... Not sure how much improvement this will give !!.
SELECT
dbo.SALESLINE.SALESID,
dbo.SALESLINE.ITEMID,
dbo.SALESLINE.QTYORDERED,
dbo.SALESLINE.LINEAMOUNT,
dbo.SALESLINE.CREATEDDATE AS datumVerkoop,
dbo.ph.CREATEDDATE AS datumInkoop,
FROM
dbo.SALESLINE
INNER JOIN
(
select
sl.salesid ,MAX(pl.recid) as LatestPurchLineId
from
salesline sl
inner join purchline pl on
sl.itemid = pl.itemid and
sl.createddate > pl.createdon and
pl.qtyordered <> 0
group by
sl.SalesId
) plat ON
dbo.salesline.salesid = plat.salesid
INNER JOIN PurchLine pl ON
pl.recid = plat.LatestPurchLineId
- Sha Anand
"Q" wrote:
> I did not create the tables myself. You probably want to know something
> about datatypes and indexes I suppose...
> salesid => varchar 20
> itemid => varchar 30
> qtyordered => numeric 13(28,12)
> lineamount => numeric 13(28,12)
> createddate => datetime 8
> The length of the datatypes are mostly larger as needed, but are not
> allowed to be changed.
> All used columns is the purchline are indexed, and the columns salesid,
> itemid are indexed.
>
Subscribe to:
Posts (Atom)