Comparing two date periods for overlapping

hi guys,

i have a booking table which has the following columns...

booking
--------------
dCheckin (format 11/9/2006 12:00:00 AM)
dCheckout (format 11/11/2006 12:00:00 AM)

when a new booking is entered, we want to make sure that the period entered does not conflict with an existing record.

not sure how to go about building the query required. any help would be greatly appreciated.

mikeDo periods that "touch" count as an overlap? Do you need to consider rooms, customers, or anything else for an overlap, or is all of the data in the table the same?

-PatP|||it does matter if they are touching eg. someone cannot checkin during a period already occupied. the other data is in another table.

m.|||ahh sorry patp. i see what you mean. touching yes it does matter. a person cannot checkin on the day someone checks out.

mike|||CREATE TABLE #patp (
id INT IDENTITY
, dCheckin DATETIME
, dCheckout DATETIME
)

INSERT INTO #patp (
dCheckin, dCheckout
) SELECT '2006-01-01', '2006-01-10'
UNION ALL SELECT '2006-01-15', '2006-01-20'
UNION ALL SELECT '2006-02-01', '2006-02-10'
UNION ALL SELECT '2006-02-10', '2006-02-15'
UNION ALL SELECT '2006-03-01', '2006-03-10'
UNION ALL SELECT '2006-03-02', '2006-03-07'
UNION ALL SELECT '2006-04-01', '2006-04-10'
UNION ALL SELECT '2006-04-08', '2006-04-13'

SELECT *
FROM #patp AS a
WHERE EXISTS (SELECT *
FROM #patp AS b
WHERE b.id != a.id -- Never compare a row with itself
AND (b.dCheckin <= a.dCheckout -- A starts before B ends
AND a.dCheckin <= b.dCheckout)) -- B ends after A starts
ORDER BY a.dCheckin

DROP TABLE #patp-PatP

Comparing to DateTimes in SQL-Select-Statement when one Date can be null

Hello!

I have a field "End" in my database that is mapped as DateTime and allows nulls. Now I want to do a SQL-Select (in a SqlDataSource) like

SELECT * FROM My_Table Where (([End] = @.EndDate) OR ([End] =null))

@.EndDate is a valid DateTime, but the second OR condition doesn't work.

What is the best way to check if the [End]-field is empty or null?

Thank you very much!

Try this: [End] IS NULL

comparing getdate()

hi I have done this before but can't seem to recall now ...I want to compare a field in my table with getdate() the field type short date which also has the time stamp...doing field_date > getdate() does not work to get data of today...can someone help!if a time is not provided when the date is entered, it defaults to midnight (when it first becomes "today"), so sometimes that can throw these types of queries off.

curtis

comparing DateTime in UK Format

Hello friends,

I am trying to return all records between 2 dates. The Date columns are in DateTime format, and i am ignoring the timestamp. The user should be able to input UK Date Format (dd/mm/yyyy) and return the rows. This sql code works fine for American date format, but i get an error: converting from varchar to datetime when i put in a UK format. eg. 22/11/06. Please advise on this problem! many thanks!

ALTER PROCEDURE SalaryBetweenDates
(

@.WeekStart datetime,

@.WeekEnd datetime
)
AS

BEGIN
SET @.WeekStart = (SELECT REPLACE(CONVERT(DATETIME,@.WeekStart ,103),' ','-'))
SET @.WeekEnd = (SELECT REPLACE(CONVERT(DATETIME,@.WeekEnd ,103),' ','-'))
END

BEGIN
SELECT s.StaffNo,s.StaffName,s.StaffAddress, s.HourlyRate,
sh.HoursWorked, CONVERT(varchar(12), sh.WeekStart, 103) AS StartDate, CONVERT(varchar(12), sh.WeekEnd, 103)As EndDate,(sh.HoursWorked * s.HourlyRate)"Salary"
From Staff As S INNER JOIN StaffHours As Sh
On S.StaffNo = Sh.StaffNo
WHERE sh.WeekStart >= (@.WeekStart)
AND sh.WeekEnd <= (@.WeekEnd)

FOR XML RAW ('paySlip'), root('Staff'), ELEMENTS XSINIL
END

ReturnYou need to convert the UK format date into a format that Sql can read.
I always use the following ones
'YYYY-MM-DD' for date
'YYYY-MM-DD HH:NN:SS' for date & time
use exactly as is... don't change the sperators

so '22/11/06' should be passed to sqlserver as '2006-11-22'|||

If you want to be able to call the procedure like this

EXEC SalaryBetweenDates '22/11/06', '1/12/06'

you're going to have to make the procedure parameters varchars and write some string handling code to figure out the strings that are passed in. I'd recommend that you leave it as it is and have the application pass dates in the format that SQL Server expects, if necessary have the application do the work at figuring out what date the user actually entered.

|||

thanks for your help guys. I set the parameters as strings in the end, and used REPLACE(CONVERT) to handle the function

:-)

Comparing Dates.

Hi All,

I have a database field (datestamp) which returns the date the records were inserted into the database. The datestamp was created with the now(); function in .net and is in the following format:5/23/2006 2:27:45 AM

I basically want to return all records that were inputted more than 28 days ago. I have had alook though some other posts and below is the closest query that i could find but unfortunately it does not work for me.

SELECT id, datestamp
FROM table
WHERE datestamp > DateAdd(d, 28, GetDate())

Thanks in advance,

Jake

You are looking for a date 28 days ago, so try -28:

SELECT id, datestamp
FROM table
WHERE datestamp > DateAdd(d,-28, GetDate())

|||

Spot on Douglas, thankyou for you quick reply!

Jake

Comparing dates with today dates

I want to know if there is a way to compare dates in the sql statement with dates that I input into a database and todays date. the datatype that I'm using is smalldatetime.
The statement I used is:
Select Date from Table where Date > 'Today.now'
I get an error
Could this be done or is there another approach?Select Date from Table where Date > getdate()
If you just want to match dates and not times:
Select Date from Table where convert(varchar(10), Date, 101) > convert(varchar(10), getDate(), 101)

Nick

Comparing Dates in SQL

I am wondering how I would create a SELECT that will select the most recent date from one of two tables. For example, table1 has a field called LastUpdate and table 2 has a field called LastUpdate. I need to grab only the most recent date. I tried this using an inner join...but that didn't work because it only picks the lastupdate form one table only. talbe1 and table2 are tied by table2.table1id.

Can anyone help?

you could do a MAX(lastupdatedate) and then do the INNER JOIN.
SELECT
table1.column
FROM
table1
INNER JOIN table2
on table1.table1id = able2.table1id
WHERE
MAX(table1.lastupdatedate) = MAX(table2.lastupdatedate)|||I'm sorry I don't understand how this works. If the last update date is today in table2, I want to return that date. Otherwise I want to return the last update date in table1. Does this mak sense?|||SELECTCASE WHEN (table1.LastUpdate > table2.LastUpdate) THEN table1.LastUpdate ELSE table2.LastUpdate END AS MaxLastUpdate
FROM ... usual join statement|||

This is almost perfect! Thanks!

Followup: what if there is no instance of table2.table1id? Then I get nothing returned but in reality I still want table1.lastupdate returned.

Thanks again!

|||Ok I'm an idiot. I just switched the when statement and the then and else so the else displays the main from table1 and walaa. Thank you so much for this. I will get better at these case statements yet!|||

You have to use OUTER JOIN to make sure all rows are included. For example (using my own dummy master/detail tables):

SELECT A.ID_MASTER, CASE WHEN (A.DateEntered > ISNULL(B.DateEntered, '01-01-1900'))
THEN A.DateEntered ELSE b.DateEntered END AS MaxLastUpdate
FROM TestDate AS A LEFT OUTER JOIN
TestDate2 AS B ON A.ID_MASTER = B.ID_MASTER

ISNULL() function is used to make sure NULL date value (for the missing row in Detail) defaults to "01-01-1900", assuming that date will be small enough. Also, this query will return multiple rows if there are more than one Detail rows for a given Master row. Depending on your situation, you may or may not have to change this.

|||

SELECT keyid,MAX(DateEntered)
FROM (
SELECT keyid,DateEntered FROM table1
UNION
SELECT keyid,DateEntered FROM table2
) z
GROUP BY keyid

Would work as well, and only return a single result for each keyid.

Comparing Dates in Previous and Current Rows

Dear All,
I am having textboxes on two detail rows (as an example), I want to calculate the date difference between two textbox date values. The date difference is between the previous row and the current row. As an example:

A B

5/2/2005 09:39:02 AM	5/3/2005 06:29:32 PM
5/4/2005 08:21:31 AM	5/5/2005 07:02:29 PM
5/6/2005 09:52:33 AM	5/6/2005 07:04:31 PM
5/7/2005 09:20:33 AM	5/8/2005 08:26:36 AM

I want to do A2-B1 in the report. I need to calculate the date difference between 5/4/2005 8:21:31 AM and 5/3/2005 6:29:32 PM as an example. The same thing for A3 and B2. ...
Do you have any idea how I can achieve this? Is there a way to do that? Do I need some special code to accomplish this?
Thank you for your help.
BTHi,
a way is to create a store proc that make the calculation
(fetching results in a temp table).|||
yes you can
you can use formula like this
=ReportItems!A.Value.Subtract(ReportItems!B.Value)
where A and B are the name of the cells in your table
noteSubtractfunction in datetime returns timespan|||

Hi,
IMHO the formula
ReportItems!A.Value.Subtract(ReportItems!B.Value)
calculate difference between value of the same row.
I've understood that Salmiya have to calculate between a value of column A with
value of column B, but of previous row.

|||Yes you are right. I have to compare previous row with current one.
Thank you.|||tblDates :
PK A B
----------------

1 5/2/2005 09:39:02 AM	5/3/2005 06:29:32 PM
2 5/4/2005 08:21:31 AM	5/5/2005 07:02:29 PM
3 5/6/2005 09:52:33 AM	5/6/2005 07:04:31 PM
4 5/7/2005 09:20:33 AM	5/8/2005 08:26:36 AM

You can do :
Select [Difference] = A-IsNull((Select B from tblDates where PK=2 ),0) from tblDates where PK=3
HTH,
Best Regards,
Hemchand|||Hi.....
U can use =ReportItems!textbox1.Value - ReportItems!textbox2.Value
Then U will get it..........
Best Regards....

Comparing dates in one field

I have a table with an ID, Date and Comments and need to compare the ID and
the date and retrieve the latest comment for a filing based on the date. So
you could have many records with the same ID that refer to the same filing,
only with a number of different dates. I've tried various things, but do no
t
get the right result.
example:
ID Date Comment
01 6/1/2006 This is the first comment
01 6/2/2006 This is the second comment *
02 6/2/2006 This is a different comment
02 6/5/2006 This is a new comment for this filing *
What I need is to get the second row with the 01 and the last row with 02 ID
in this example indicated by *.
I'm using SQL Server 2000. Thanks for your help in advance.CREATE TABLE #Temp ([ID] VARCHAR(2),
[Date] DATETIME,
[Comment] VARCHAR(2000),
PRIMARY KEY ([ID], [Date]))
INSERT INTO #Temp ([ID], [Date], [Comment])
SELECT '01', '2006-06-01', 'This is the first comment'
UNION SELECT '01', '2006-06-02', 'This is the second comment*'
UNION SELECT '02', '2006-06-02', 'This is a different comment'
UNION SELECT '02', '2006-06-05', 'This is a new comment for this filing'
UNION SELECT '02', '2006-06-06', 'This is a newer comment'
UNION SELECT '03', '2006-06-01', 'New ID, New comment*'
UNION SELECT '02', '2006-07-01', 'The newest comment*'
SELECT t1.[ID], t1.[Date], t1.[Comment]
FROM #Temp t1
WHERE t1.[Date] =
(
SELECT MAX([Date])
FROM #Temp t2
WHERE t2.[ID] = t1.[ID]
)
GROUP BY t1.[ID], t1.[Date], t1.[Comment]
DROP TABLE #Temp
"SK" <SK@.discussions.microsoft.com> wrote in message
news:3B67DA49-A987-44A9-B403-8CA85D581817@.microsoft.com...
>I have a table with an ID, Date and Comments and need to compare the ID and
> the date and retrieve the latest comment for a filing based on the date.
> So
> you could have many records with the same ID that refer to the same
> filing,
> only with a number of different dates. I've tried various things, but do
> not
> get the right result.
> example:
> ID Date Comment
> 01 6/1/2006 This is the first comment
> 01 6/2/2006 This is the second comment *
> 02 6/2/2006 This is a different comment
> 02 6/5/2006 This is a new comment for this filing *
> What I need is to get the second row with the 01 and the last row with 02
> ID
> in this example indicated by *.
> I'm using SQL Server 2000. Thanks for your help in advance.|||Try this..
SELECT ID,Date,Comment FROM
YourTable WHERE Date =
(SELECT MAX(Date) FROM YourTable yt1 WHERE YourTable.Id = yt1.Id)
- Sha Anand
"SK" wrote:

> I have a table with an ID, Date and Comments and need to compare the ID an
d
> the date and retrieve the latest comment for a filing based on the date.
So
> you could have many records with the same ID that refer to the same filing
,
> only with a number of different dates. I've tried various things, but do
not
> get the right result.
> example:
> ID Date Comment
> 01 6/1/2006 This is the first comment
> 01 6/2/2006 This is the second comment *
> 02 6/2/2006 This is a different comment
> 02 6/5/2006 This is a new comment for this filing *
> What I need is to get the second row with the 01 and the last row with 02
ID
> in this example indicated by *.
> I'm using SQL Server 2000. Thanks for your help in advance.|||Hi there
One query that you can try is:
SELECT A.ID, A.Date, A.Comment
FROM dbo.[Comments] A
INNER JOIN (SELECT ID, MAX(Date) FROM dbo.[Comments] GROUP BY ID) B
ON A.ID = B.ID
Lucas
"SK" wrote:

> I have a table with an ID, Date and Comments and need to compare the ID an
d
> the date and retrieve the latest comment for a filing based on the date.
So
> you could have many records with the same ID that refer to the same filing
,
> only with a number of different dates. I've tried various things, but do
not
> get the right result.
> example:
> ID Date Comment
> 01 6/1/2006 This is the first comment
> 01 6/2/2006 This is the second comment *
> 02 6/2/2006 This is a different comment
> 02 6/5/2006 This is a new comment for this filing *
> What I need is to get the second row with the 01 and the last row with 02
ID
> in this example indicated by *.
> I'm using SQL Server 2000. Thanks for your help in advance.|||Thank you very much for your quick response! It seems to work perfectly!
I had the second Where clause in the wrong place!
"Sha Anand" wrote:
> Try this..
> SELECT ID,Date,Comment FROM
> YourTable WHERE Date =
> (SELECT MAX(Date) FROM YourTable yt1 WHERE YourTable.Id = yt1.Id)
> - Sha Anand
>
> "SK" wrote:
>|||Wow! This was quite fast and thorough!
I've never tried it this way before with Union Select. But it works
beautifully.
Thank you Mike for taking the time to go to such length! I Appreciate it!
Have a lovely day!
SK
"Mike C#" wrote:

> CREATE TABLE #Temp ([ID] VARCHAR(2),
> [Date] DATETIME,
> [Comment] VARCHAR(2000),
> PRIMARY KEY ([ID], [Date]))
> INSERT INTO #Temp ([ID], [Date], [Comment])
> SELECT '01', '2006-06-01', 'This is the first comment'
> UNION SELECT '01', '2006-06-02', 'This is the second comment*'
> UNION SELECT '02', '2006-06-02', 'This is a different comment'
> UNION SELECT '02', '2006-06-05', 'This is a new comment for this filing'
> UNION SELECT '02', '2006-06-06', 'This is a newer comment'
> UNION SELECT '03', '2006-06-01', 'New ID, New comment*'
> UNION SELECT '02', '2006-07-01', 'The newest comment*'
> SELECT t1.[ID], t1.[Date], t1.[Comment]
> FROM #Temp t1
> WHERE t1.[Date] =
> (
> SELECT MAX([Date])
> FROM #Temp t2
> WHERE t2.[ID] = t1.[ID]
> )
> GROUP BY t1.[ID], t1.[Date], t1.[Comment]
> DROP TABLE #Temp
> "SK" <SK@.discussions.microsoft.com> wrote in message
> news:3B67DA49-A987-44A9-B403-8CA85D581817@.microsoft.com...
>
>

Comparing dates in Case statement

hi

I am having problem in converting dates either to varchar or integer

I need to compare (just month and date part , not the year)

If

mm/dd (of current date) i.e 07/12 > 06/30

i.e if today's month and date is greater than june 30th then perform task A

if today's month and date is less than july 1st perform task B

i.e

07/12 < 07/01

please help

Thanks

Code Snippet

select case when (month(getdate())* 100 + day(getdate())) > 0630

then ... -- task a

else ... --task b

end

from ....

|||

Thank you very much , that helped

But I have a new problem

I am trying to execute task A

as below

SELECT
case
when (month(getdate())* 100 + day(getdate())) > 0630
then
(select * from dbo.V_FUN_SCOPES_LEVELS0506)
end

It gives error

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

|||

You need to use IF syntax instead of CASE syntax; give a look to the related article in books online. That should look something like:

Code Snippet

if (month(getdate())* 100 + day(getdate())) > 0630
select * from dbo.V_FUN_SCOPES_LEVELS0506

Also, beware of using "SELECT *" syntax from within a stored procedure or function.

|||

yea, it won't work in that manner.

Can you elaborate a little more on what you're trying to do and how it is going to be used.

From this is looks like you'll need a stored procedure or maybe a function.

|||

I think in you're probably just looking at some standard IF...ELSE control of flow.

IF (SELECT (month(getdate())* 100 + day(getdate()))) > 0630

PRINT 'do this'

ELSE

PRINT 'do that'

|||

Hi

the IF syntax works independently but If i try to create a store proc as below it gives error

CREATE PROCEDURE [dbo].[P_Latest]

AS

IF (month(getdate())* 100 + day(getdate())) > 0630
BEGIN
select * from dbo.V_FUN_SCOPES_LEVELS0506

END

ELSE
IF (month(getdate())* 100 + day(getdate())) < 0701
BEGIN
select * from dbo.V_FUN_SCOPES_LEVELS0506
END

G0

I need to display select * from PROCEDURE [dbo].[P_Latest]

i.e Exec PROCEDURE [dbo].[P_Latest] from SQL reporting services

P.S. Here the view dbo.V_FUN_SCOPES_LEVELS0506 is as below

I need the subquery for Display purpose

(select
a.student_id,
a.at_sss_read_score as Score_06,
b.at_sss_read_score as Score_07,
a.at_test_month + '/' + '20' + a.fcat_test_year as Date_06,
b.at_test_month + '/' + '20' + b.fcat_test_year as Date_07,
a.at_test_month as Month_06,
b.at_test_month as Month_07,
a.at_test_year as Year_06,
b.at_test_year as Year_07,
a.at_sss_read_level as Level_06,
b.at_sss_read_level as Level_07

from
(
(select student_id, at_sss_read_score ,at_test_month,at_test_year,at_sss_read_level
from
DW_STUDENT.DBO.AT_TEST
where AT_TEST_YEAR = right (year (getdate())-1 , 2)) a
--AND STUDENT_ID IN ('0011307')) a

full join

(select student_id, at_sss_read_score ,at_test_month,at_test_year,at_sss_read_level
from
DW_STUDENT.DBO.AT_TEST
where AT_TEST_YEAR = right (year (getdate()),2 )) b
--AND STUDENT_ID IN ('0011307')) b

on a.student_id = b.student_id
)

where
a.at_test_year is not null
and b.at_test_year is not null

)

|||

This might work:

Code Snippet

CREATE PROCEDURE [dbo].[P_Latest]
AS

IF (month(getdate())* 100 + day(getdate())) > 0630
select * from dbo.V_FUN_SCOPES_LEVELS0506
ELSE
select * from dbo.V_FUN_SCOPES_LEVELS0506

G0

But I feel like I am missing something major. I see no point to the IF statement nor the ELSE Statement. Also, the SELECT * from inside a procedure is a bad idea because the MEANING of the SELECT * statement is determined at compile time and not at run time. You should explicitly list the columns that you return.

I feel like I am going wrong with this. Anyone? Help?

|||

In your Reporting Services dataset, just set the command type to StoredProcedure and the QueryString to [dbo].[P_Latest].

That is the equivalent of select * from ...

|||

I chose to believe that that was just test code

|||

Kent Waldrop Jl07 wrote:

This might work:

Code Snippet

CREATE PROCEDURE [dbo].[P_Latest]
AS

IF (month(getdate())* 100 + day(getdate())) > 0630
select * from dbo.V_FUN_SCOPES_LEVELS0506
ELSE
select * from dbo.V_FUN_SCOPES_LEVELS0506

G0

But I feel like I am missing something major. I see no point to the IF statement nor the ELSE Statement. Also, the SELECT * from inside a procedure is a bad idea because the MEANING of the SELECT * statement is determined at compile time and not at run time. You should explicitly list the columns that you return.

I feel like I am going wrong with this. Anyone? Help?

I've always heard this... "...SELECT * from inside a procedure is a bad idea.." without really understanding why. I apologize if i'm deviating from the point but...

when you say compile time and not run time, do you mean if a store procedure is compiled with select * then subsequently, the table changes (ie. column added) the sp will return everything without the extra column?

|||Yes, that is exactly what I mean; it is for that reason that I will sometimes say that use of "SELECT *" leaves "land mines" that blow up sometime later.

comparing dates

Hi all,
I'm trying to compare two dates, from two tables. Both are of datetime
datatype.
I dont want the time portion to be involved in the date, as I only need to
compare days. Having the datetime in the comparison yeilds unexpected
results
Thanks
RobertRobert Bravery wrote:
> Hi all,
> I'm trying to compare two dates, from two tables.
In a join statement?

> Both are of datetime
> datatype.
> I dont want the time portion to be involved in the date, as I only
> need to compare days. Having the datetime in the comparison yeilds
> unexpected results
>
You could simply use CONVERT on both datetime columns:
... CONVERT(char(8),table1.datecol,112) =
CONVERT(char(8),table2.datecol,112)
but that would prevent an existing index from being used on both tables
resulting in poor performance. An alternative that may perform better,
especially if an index exists for the datetime coumn in table 1, would be
this:
... table1.datecol >=dateadd(d,datediff(d,0,table2.datecol),0) and
table1.datecol < dateadd(d,1 + datediff(d,0,table2.datecol),0)
HTH,
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Convert the dates to a ISO date, thats my best practise for this.
CONVERT(VARCHAR(10),GETDATE(),112)
HTH, jens Suessmeyer.|||Compare after taking off the time part of DATETIME field...
SELECT getdate()
SELECT CAST(FLOOR(CAST( getdate() AS float)) AS DATETIME)
Thanks,
Sree
"Jens" wrote:

> Convert the dates to a ISO date, thats my best practise for this.
> CONVERT(VARCHAR(10),GETDATE(),112)
> HTH, jens Suessmeyer.
>|||Hi Bob,
Thanks for the response.
Could I well use, youre response gave me an idea, if
convert(int,@.dol,112)>=convert(int,@.sdate+1,112)
@.dol and @.sdate are both datetime datatypes
Would that comparison work
Thanks
Robert
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:OCeiNByKGHA.536@.TK2MSFTNGP09.phx.gbl...
> Robert Bravery wrote:
> In a join statement?
>
> You could simply use CONVERT on both datetime columns:
> ... CONVERT(char(8),table1.datecol,112) =
> CONVERT(char(8),table2.datecol,112)
> but that would prevent an existing index from being used on both tables
> resulting in poor performance. An alternative that may perform better,
> especially if an index exists for the datetime coumn in table 1, would be
> this:
> ... table1.datecol >=dateadd(d,datediff(d,0,table2.datecol),0) and
> table1.datecol < dateadd(d,1 + datediff(d,0,table2.datecol),0)
> HTH,
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>|||Robert Bravery wrote:
> Hi Bob,
> Thanks for the response.
> Could I well use, youre response gave me an idea, if
> convert(int,@.dol,112)>=convert(int,@.sdate+1,112)
> @.dol and @.sdate are both datetime datatypes
I thought we were dealing with columns, not variables ... ?

> Would that comparison work
>
Did you try it? If you had, you would have answered your own question.
Yes, that will work, but the ",112" part will be ignored. When converting
the datetime to int, you will get the number of days since the seed date,
run this script to see:
select convert(int,getdate(),112),convert(int,g
etdate())
Steve Kass pointed out to me a while back that there is a performance impact
involved in converting a datetime to another datatype, which is why I used
the dateadd(d,datediff ... technique in my post, which Steve showed to be
faster.
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||HI Bob
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message news:%
> I thought we were dealing with columns, not variables ... ?
We are and we arent. I need to compare a few things, all involve variables
and columns, and a combination of them

> Did you try it? If you had, you would have answered your own question.
I did. ANd it did seem to work. Just wanted some confirmation that I was
doing the right thing or doing t the right way.

> Yes, that will work, but the ",112" part will be ignored. When converting
> the datetime to int, you will get the number of days since the seed date,
> run this script to see:
> select convert(int,getdate(),112),convert(int,g
etdate())
> Steve Kass pointed out to me a while back that there is a performance
impact
> involved in converting a datetime to another datatype, which is why I used
> the dateadd(d,datediff ... technique in my post, which Steve showed to be
> faster.
>
Thanks FOr this
Robert|||Thanks
Robert
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:48FD7950-C281-4B31-BB69-B38C76A77EC2@.microsoft.com...
> Compare after taking off the time part of DATETIME field...
> SELECT getdate()
> SELECT CAST(FLOOR(CAST( getdate() AS float)) AS DATETIME)
>
> Thanks,
> Sree
>
> "Jens" wrote:
>

Comparing dates

Hello!
I want to check if a date (not time) is found in a column with datetime.
If I have the following rows in a table:
2005-02-02 02:58:00
2005-02-02 07:22:30
2005-01-28 11:33:56
I want to find rows using a stored procedure with the @.date parameter set to
2005-02-02, but within all time during that day.
So with one criteria I want to find the to rows with 2005-02-02...
I don't want to use text, because I want the application and database to run
on different servers in different countries with different configuration for
date format.
This must be a very common task, but I can't figure out how to solve it, so
please help...
Regards MagnusDECLARE @.MyDateParameter char(8)
SET @.MyDateParameter = '20050209'
SELECT *
FROM MyTable
WHERE CONVERT(CHAR(8),MyDateColumn,112) = @.MyDateParameter
"Magnus Blomberg" <magnus.blomberg@.skanska.se> wrote in message
news:%23W7sRAvDFHA.1392@.tk2msftngp13.phx.gbl...
> Hello!
> I want to check if a date (not time) is found in a column with datetime.
> If I have the following rows in a table:
> 2005-02-02 02:58:00
> 2005-02-02 07:22:30
> 2005-01-28 11:33:56
> I want to find rows using a stored procedure with the @.date parameter set
> to
> 2005-02-02, but within all time during that day.
> So with one criteria I want to find the to rows with 2005-02-02...
> I don't want to use text, because I want the application and database to
> run
> on different servers in different countries with different configuration
> for
> date format.
> This must be a very common task, but I can't figure out how to solve it,
> so
> please help...
> Regards Magnus
>|||Hi,
try using datediff function eg :
--
Use Northwind
Go
DECLARE @.date as datetime
Set @.date = '04-Jul-1996 19:45:23'
SELECT @.date
SELECT * from Orders
where Datediff(day,Orders.OrderDate,@.date) =0
Let us know if it helps
siaj
"Magnus Blomberg" wrote:

> Hello!
> I want to check if a date (not time) is found in a column with datetime.
> If I have the following rows in a table:
> 2005-02-02 02:58:00
> 2005-02-02 07:22:30
> 2005-01-28 11:33:56
> I want to find rows using a stored procedure with the @.date parameter set
to
> 2005-02-02, but within all time during that day.
> So with one criteria I want to find the to rows with 2005-02-02...
> I don't want to use text, because I want the application and database to r
un
> on different servers in different countries with different configuration f
or
> date format.
> This must be a very common task, but I can't figure out how to solve it, s
o
> please help...
> Regards Magnus
>
>|||On Wed, 9 Feb 2005 22:39:16 +0100, Magnus Blomberg wrote:

>I want to find rows using a stored procedure with the @.date parameter set t
o
>2005-02-02, but within all time during that day.
Hi Magnus,
You've already gotten some suggestions that will work, but if there's an
index on the datetime column, you should use this one instead:
SELECT ...
FROM ...
WHERE MyDateCol >= @.date
AND MyDateCol < DATEADD(day, 1, @.date)
An index can only be used if the indexed column is on it's own on one side
of a comparison operator - if it's in a function or other expression, the
index can't be used to quickly locate the rows you need.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello!
Thanks all of you.
I think all of your suggestions will work, but I'll go for the DateDiff
variant.
Regards Magnus
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

Comparing DATE with TIMESTAMP fields possible ?

In my database table there is a datetime column which can obviously contain
a timestamp
(in the format YYYY-MM-DD HH:MM:SS.NNN).
Now I want to get all those records which have a date of lets say 2005-02-03
regardsless of
their time value.
Ok, I could do this by statement like
SELECT * FROM MYTAB WHERE MYDATE >= '2005-02-03 00:00:00.000' AND MYDATE <=
'2005-02-03 23:59.59.999'
but this is rather inconvenient. I feel that there must be a shorter version
like
SELECT * FROM MYTAB WHERE MYDATE = '2005-02-03 **:**:**.***'
or a built-in fuction like:
SELECT * FROM MYTAB WHERE datepartonly(MYDATE) = '2005-02-03'
How does this work?
GeorgeHi
What you are looking for:
SELECT * FROM MYTAB WHERE CONVERT(CHAR(10), MYDATE, 120) = '2005-02-03'
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"George Dainis" <george.dainis@.bluecorner.com> wrote in message
news:cue1up$k5b$01$1@.news.t-online.com...
> In my database table there is a datetime column which can obviously
contain a timestamp
> (in the format YYYY-MM-DD HH:MM:SS.NNN).
> Now I want to get all those records which have a date of lets say
2005-02-03 regardsless of
> their time value.
> Ok, I could do this by statement like
> SELECT * FROM MYTAB WHERE MYDATE >= '2005-02-03 00:00:00.000' AND MYDATE
<= '2005-02-03 23:59.59.999'
> but this is rather inconvenient. I feel that there must be a shorter
version like
> SELECT * FROM MYTAB WHERE MYDATE = '2005-02-03 **:**:**.***'
> or a built-in fuction like:
> SELECT * FROM MYTAB WHERE datepartonly(MYDATE) = '2005-02-03'
> How does this work?
> George
>|||> SELECT * FROM MYTAB WHERE MYDATE >= '2005-02-03 00:00:00.000'
> AND MYDATE <= '2005-02-03 23:59.59.999'
In fact this won't give the answer you want. '2005-02-03 23:59.59.999'
will be rounded up to 2005-02-04!
Instead try:
SELECT *
FROM MYTAB
WHERE mydate >= '20050203'
AND mydate < '20050204'
Note also that the date format you used is not safe under all regional
connection settings - it may cause a syntax error. The safe formats
are:
'2005-02-03T00:00:00.000'
'2005-02-03T00:00:00'
'20050203'
David Portas
SQL Server MVP
--|||On Wed, 9 Feb 2005 23:13:46 +0100, George Dainis wrote:

>In my database table there is a datetime column which can obviously contain
a timestamp
>(in the format YYYY-MM-DD HH:MM:SS.NNN).
>Now I want to get all those records which have a date of lets say 2005-02-0
3 regardsless of
>their time value.
(snip)
You've already gotten some suggestions that will work, but if there's an
index on the datetime column, you should use this one instead:
SELECT ...
FROM ...
WHERE MyDateCol >= '20050203' -- Standard date format has no dashes!
AND MyDateCol < '20050204'
An index can only be used if the indexed column is on it's own on one side
of a comparison operator - if it's in a function or other expression, the
index can't be used to quickly locate the rows you need.
Your suggested query:

>SELECT * FROM MYTAB WHERE MYDATE >= '2005-02-03 00:00:00.000' AND MYDATE <= '2005-0
2-03 23:59.59.999'
will also return the rows with mydate equal to 2005-02-04 00:00:00.000, as
datetime has a precision of 3/1000th of a second - 23:59:59.999 gets
rounded up to 0:00:00.000, not down to 23:59:59.997.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||George Dainis wrote:

> In my database table there is a datetime column which can obviously contai
n a timestamp
> (in the format YYYY-MM-DD HH:MM:SS.NNN).
> Now I want to get all those records which have a date of lets say 2005-02-
03 regardsless of
> their time value.
> Ok, I could do this by statement like
> SELECT * FROM MYTAB WHERE MYDATE >= '2005-02-03 00:00:00.000' AND MYDATE <
= '2005-02-03 23:59.59.999'
> but this is rather inconvenient. I feel that there must be a shorter versi
on like
> SELECT * FROM MYTAB WHERE MYDATE = '2005-02-03 **:**:**.***'
> or a built-in fuction like:
> SELECT * FROM MYTAB WHERE datepartonly(MYDATE) = '2005-02-03'
> How does this work?
> George
What you are suggesting above is implicit conversion which Oracle
advises against and which is likely to lead to many problems. A
date is not a string and a timestamp is not a string. So surrounding
them with single-quotes is a bad idea in many respects.
What you need to look at is the following functions:
TO_CHAR, TO_DATE, and TO_TIMESTAMP.
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace 'x' with 'u' to respond)|||On Wed, 09 Feb 2005 15:52:39 -0800, DA Morgan wrote:
(snip)
>What you are suggesting above is implicit conversion which Oracle
>advises against and which is likely to lead to many problems. A
>date is not a string and a timestamp is not a string. So surrounding
>them with single-quotes is a bad idea in many respects.
>What you need to look at is the following functions:
>TO_CHAR, TO_DATE, and TO_TIMESTAMP.
Hi DA,
This is a SQL Server newsgroup. TO_CHAR, TO_DATE, and TO_TIMESTAMP are not
valid functions in SQL Server.
In SQL Server, implicit conversion from string to datetime is perfectly
valid, as long as unambiguous date and datetime formats are used:
yyyymmdd
yyyy-mm-ddThh:mm:ss
yyyy-mm-ddThh:mm:ss.ttt
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||George Dainis wrote:
> In my database table there is a datetime column which can obviously contai
n a timestamp
> (in the format YYYY-MM-DD HH:MM:SS.NNN).
> Now I want to get all those records which have a date of lets say 2005-02-
03 regardsless of
> their time value.
> Ok, I could do this by statement like
> SELECT * FROM MYTAB WHERE MYDATE >= '2005-02-03 00:00:00.000' AND MYDATE <
= '2005-02-03 23:59.59.999'
> but this is rather inconvenient. I feel that there must be a shorter versi
on like
> SELECT * FROM MYTAB WHERE MYDATE = '2005-02-03 **:**:**.***'
> or a built-in fuction like:
> SELECT * FROM MYTAB WHERE datepartonly(MYDATE) = '2005-02-03'
> How does this work?
> George
>
Morning George,
select * from mytab
where trunc(mydate) = to_date('2005-02-03', 'yyyy-mm-dd');
However, if mydate is indexed then the trunc() call will prevent the
index being used, so you need to use a statement similar to the one you
have mentioned above except I would use BETWEEN instead of >= and <= but
that's just personal preference.
Cheers,
Norm.
PS. Oracle stores DATE and TIMESTAMP columns in it's own internal
format, not the format you 'think' it is stored in. Always, when doing
date stuff, specify the format mask to TO_DATE because one day your code
may be running on a database which has a different default date format
and it will barf. Been there, got bitten, fixed it.|||On Thu, 10 Feb 2005 07:48:22 +0100, Norman Dunbar wrote:
(snip)
>select * from mytab
>where trunc(mydate) = to_date('2005-02-03', 'yyyy-mm-dd');
Hi Norman,
The result of this query will be:
Server: Msg 195, Level 15, State 10, Line 2
'trunc' is not a recognized function name.
Why are you posting Oracle syntax as replies to questions in a MS SQL
Server newsgroup?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Thu, 10 Feb 2005 06:13:46 +0800, George Dainis wrote
(in article <cue1up$k5b$01$1@.news.t-online.com> ):

> In my database table there is a datetime column which can obviously contain a[/col
or]
> timestamp
> (in the format YYYY-MM-DD HH:MM:SS.NNN).
> Now I want to get all those records which have a date of lets say 2005-02-
03
> regardsless of
> their time value.
> Ok, I could do this by statement like
> SELECT * FROM MYTAB WHERE MYDATE >= '2005-02-03 00:00:00.000' AND MYDATE <=[/color
]
> '2005-02-03 23:59.59.999'
> but this is rather inconvenient. I feel that there must be a shorter versi
on
> like
> SELECT * FROM MYTAB WHERE MYDATE = '2005-02-03 **:**:**.***'
> or a built-in fuction like:
> SELECT * FROM MYTAB WHERE datepartonly(MYDATE) = '2005-02-03'
> How does this work?
> George
>
use the between function.|||George Dainis wrote:
> In my database table there is a datetime column which can obviously contai
n a timestamp
> (in the format YYYY-MM-DD HH:MM:SS.NNN).
> Now I want to get all those records which have a date of lets say 2005-02-
03 regardsless of
> their time value.
> Ok, I could do this by statement like
> SELECT * FROM MYTAB WHERE MYDATE >= '2005-02-03 00:00:00.000' AND MYDATE <
= '2005-02-03 23:59.59.999'
> but this is rather inconvenient. I feel that there must be a shorter versi
on like
> SELECT * FROM MYTAB WHERE MYDATE = '2005-02-03 **:**:**.***'
> or a built-in fuction like:
> SELECT * FROM MYTAB WHERE datepartonly(MYDATE) = '2005-02-03'
> How does this work?
> George
>
TRUNC is your friend, by default it will truncate the date part to the
DD-MON-YY part, dropping the time area, so:
CREATE TABLE mytab (mydate timestamp);
INSERT INTO mytab VALUES (SYSDATE); <-- few entries over a few seconds.
SELECT * FROM mytab WHERE TRUNC(mydate) = '12-FEB-05';
Hope that helps,
Enoch.

Comparing Date to SQL Server Date

Hey everyone...I need to create a job which occurs once monthly that is based on a stored procedure that contains as a rough example:

CREATE PROCEDURE CompareDate
@.CurrentDate datetime

AS

SELECT * FROM Contracts WHERE EndDate LIKE (CURRENT SERVER DATE)
GO

How do I declare a parameter to automatically get the current server date in the stored procedure? Also, once it is declared how can I add Months to it? For example this job is executed monthly and is supposed to select contracts that expire 2 months from the current month. So once I get the current server date, how do I add 2 months to the date in the stored procedure? Thanks very much in advance!!!Use getdate() function to get the current server date.

Comparing Date ranges

I have two sets of dates to work with. One is an existing booking with a start and an end date. The other is a new booking with a start and an end date. I want to compare them and calculate how much overlap there is. If the overlap is over a certain amount (say 4 days), then I want to flag the user.

Is there any thing I can use in terms of a SQL query to assist in this comparison? I'm relatively new to SQL so I'm not entirely sure what functions and keywords are available to me to make this comparison.BOL (Books On Line) is the best source for some date functions..they also have sample codes..

hth|||You can you the DATEDIFF(datepart, datetime1,datetime2) function to get the difference between two dates.
Take a look at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_2c1f.asp
for SQL Date Function.|||I actually ended up checking whether the start and end dates of each set were with in a certain range using a BETWEEN X AND Y syntax.

Comparing date only?

This is probably a really simple one:
Given a table with a DATETIME column, how can I do an = or <> comparison
with just the date part?
ie.
SELECT * FROM MyTable WHERE SOMEFUNC(MyTable.theDateTime) <>
SOMEFUNC(@._In_theDataTime)
, where SOMEFUNC will just return the date part (ignoring the time part) of
theDateTime.
Thanks.....You don't want to apply expressions to both sides of the WHERE clause,
otherwise you will negate any indexes. How about (assuming your oddly named
variable is a DATETIME or, better yet, SMALLDATETIME):
WHERE MyTable.TheDateTIme >= (@._In_theDataTime)
AND MyTable.TheDateTIme < (@._In_theDataTime + 1)
"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:dbo636$hgi$1$8302bc10@.news.demon.co.uk...
> This is probably a really simple one:
> Given a table with a DATETIME column, how can I do an = or <> comparison
> with just the date part?
> ie.
> SELECT * FROM MyTable WHERE SOMEFUNC(MyTable.theDateTime) <>
> SOMEFUNC(@._In_theDataTime)
> , where SOMEFUNC will just return the date part (ignoring the time part)
> of theDateTime.
>
> Thanks.....
>|||Okay, thanks (and yes, my stored proc names are odd, as I use:
@._In_somevar, @._Out_somevar, @._InOut_somevar). I find it easier to
distinguish between local vars and parameters just by prefixing parameters
with `_'. It's a personal thing (no guidelines here) - also, quite often I
write Data when I mean to write Date and visa versa - apologies as I'm
slightly dyslexic. Anyway:
WHERE MyTable.TheDateTime >= (@._In_theDateTime)
AND MyTable.TheDateTime < (@._In_theDateTime + 1)
This won't work though, because @._In_theDateTime might be less than
MyTable.TheDateTime but still on the same date (ie. the former is 3pm, the
latter 1pm). I was thinking I would be able to say "DATEPART(x) =
DATEPART(y)" or something. I don't have millions of rows (average around
20 - 40k rows in this table), so I don't mind too much about not using the
indexes (at least for now).
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eyawDVfjFHA.3336@.tk2msftngp13.phx.gbl...
> You don't want to apply expressions to both sides of the WHERE clause,
> otherwise you will negate any indexes. How about (assuming your oddly
> named variable is a DATETIME or, better yet, SMALLDATETIME):
> WHERE MyTable.TheDateTIme >= (@._In_theDataTime)
> AND MyTable.TheDateTIme < (@._In_theDataTime + 1)
>
>
> "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
> message news:dbo636$hgi$1$8302bc10@.news.demon.co.uk...
>|||> This won't work though, because @._In_theDateTime might be less than
> MyTable.TheDateTime but still on the same date (ie. the former is 3pm, the
> latter 1pm).
So why not pass in JUST THE DATE?
Or, in the stored procedure, say:
SET @._in_theDateTime = 0 + DATEDIFF(DAY, 0, @._in_TheDateTime)
-- this will set the time portion to midnight without any messy
casts/conversions.|||See CONVERT function in SQL Server Books Online. It offers you various
styles for date formatting. One of them (112) gets rid of time portion.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:dbo636$hgi$1$8302bc10@.news.demon.co.uk...
This is probably a really simple one:
Given a table with a DATETIME column, how can I do an = or <> comparison
with just the date part?
ie.
SELECT * FROM MyTable WHERE SOMEFUNC(MyTable.theDateTime) <>
SOMEFUNC(@._In_theDataTime)
, where SOMEFUNC will just return the date part (ignoring the time part) of
theDateTime.
Thanks.....|||Okay I get it now - yes this will work. Thanks.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23vvTFhfjFHA.3608@.TK2MSFTNGP12.phx.gbl...
> So why not pass in JUST THE DATE?
> Or, in the stored procedure, say:
> SET @._in_theDateTime = 0 + DATEDIFF(DAY, 0, @._in_TheDateTime)
> -- this will set the time portion to midnight without any messy
> casts/conversions.
>sqlsql

Comparing a Date Value in SQL against Todays Date

I have a query that accesses a series of events in a gridview and would like to only show those that are in the future. I am looking to compare the date/time column against today's date in my SQL query.

Any ideas would be great.

Thanks
Orbital

you can use the t-sql function: getdate() in your query

sqlsql

compare two different date format column?

Hi guys,

I want to compare two date fields, which are located on different tables. One field contains a date value in this format: ###/###-#### and the other field on the other table contains (###-###-#### and some ##########)

So I want to compare this two different format. May be changing both the formats to a common format and make the comparison, something like that.

Any idea is appreciate.

Hi Amde

Just for clarity, would you be able to provide real examples (from your tables) of a date in each format?

Thanks
Chris

|||

oh yeah,

Table 1.

Phone no.

847/678-2828

Table 2.

Phone no.

333-777-5555

252 321 3333

2223334444

(123)456-789

...

|||

For phone numbers, I would consider creating a Function that strips out all but the numbers -and then saving and comparing only numbers. (Leave the formating for the client application.)

Comparing DATE fields will be somewhat different -however formating is not an issue if the datatype is (small)datetime. If Date data is stored as (var)char -that is a whole different can of worms.

|||

Well that changes things slightly - you originally said that these were date fields...

Another couple of questions - how many rows of data are you looking to compare? Are there any other formats that we should know about?

Thanks
Chris

|||

I've just put together the function below that will strip any non-numerical characters from an input string.

Don't expect fantastic performance from the function when comparing thousands of rows. It would be far better, as Arnie highlighted, to store an unformatted string and leave the formatting to the client.

Chris

CREATE FUNCTION dbo.CleanString(@.InputString VARCHAR(8000))
RETURNS VARCHAR(8000)
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
DECLARE @.NewString VARCHAR(8000)
SET @.NewString = ''
DECLARE @.ValidChars CHAR(10)
SET @.ValidChars = '0123456789'
DECLARE @.Loop INT
SET @.Loop = 1
WHILE @.Loop <= LEN(@.InputString)
BEGIN
IF CHARINDEX(SUBSTRING(@.InputString, @.Loop, 1), @.ValidChars) > 0
SET @.NewString = @.NewString + SUBSTRING(@.InputString, @.Loop, 1)

SET @.Loop = @.Loop + 1
END

RETURN @.NewString
END
GO

SELECT dbo.CleanString('1329842DCXLXMC2QPWXMQWX09SWDM09324')
--Returns: 132984220909324

SELECT CASE WHEN dbo.CleanString('1M&&2-FL3FCM4+=5M#6khn7wmx8KE9AAS0')
= dbo.CleanString('cxmn1dUI2QWWJHB3NCU4PWCK5IVN67DBW8XW9K 0')
THEN 'These strings are the same when cleansed.'
END,
dbo.CleanString('1M&&2-FL3FCM4+=5M#6khn7wmx8KE9AAS0'),
dbo.CleanString('cxmn1dUI2QWWJHB3NCU4PWCK5IVN67DBW8XW9K 0')
--Returns: 'These strings are the same when cleansed.', 1234567890, 1234567890

|||

Oh!my bad, I am really sorry.

- I want to say a phone number (which is varchar data type)

- The first table contain about 40,000 records and the second table contains about 60, 000 records. So I want to get the difference.

- Those are the common formats. I hope if I find a solution for one of the format, I can apply similar logic.

Sorry for the confusion.

|||

For a large amount of data, avoiding the loop may increase performance.

By using a 'Numbers' table, this method avoids looping.

-- Prepare Numbers Table

SET NOCOUNT ON
CREATE TABLE Numbers ( n int )
GO

DECLARE @.n int
SET @.n = 1
WHILE @.n < 100
BEGIN
INSERT INTO Numbers VALUES ( @.n )
SET @.n = ( @.n + 1 )
END
-- End Numbers Table

CREATE FUNCTION dbo.NumbersOnly
( @.Text varchar(100) )
RETURNS varchar(100)
AS
BEGIN
DECLARE @.Output varchar(100)
SELECT @.Output = ''
SELECT @.Output = @.Output + CASE
WHEN RealNum LIKE '[0-9]'
THEN RealNum
ELSE ''
END
FROM (SELECT substring( @.Text, n, 1 ) RealNum
FROM Numbers
) d
RETURN @.Output
END
GO

SELECT dbo.NumbersOnly( '(555) 987-4321' )

--
5559874321

|||

You should clean the data and store it in a normalized manner. This is the best way to handle. Solutions using UDFs are fine but they will be slow and you will have to keep changing them if you find newer formats or rules. For example, how do you handle phone numbers like 1-800-96MSFAX? What about phone numbers from other countries etc? There are so many cases where hard-coded solutions break and you will end up with more problems than necessary. I have built data warehouses in the past (in my previous company for data mining/analytics purpose) and we used public geography data, address book scrubbing software etc. So it is better to invest in a solution or package that is specialized for the type of data you are dealing with and focus on your business problem. Once you normalize the phone numbers then it is very easy to compare them - you can easily restrict phone numbers based on country code for example or compare different formats easily.

|||

I agree with you. Having a normalized database is the best thing. However, I want to do this task only for the time being. Here is the thing, I received an excel sheet which contains thousands of records. On the other hand we have a table in our database. So my goal is to get the difference and insert the new data to my table. So what I did is I created an SSIS package to migrate the excel sheet data into SQL Server table, so that it will be easy to write a query to get the differential data. Inserting the data is not an issue. All I need is to compare these two tables and get the different. The only unique field I have to do the comparison is the PHONE column. And the PHONE column has a different format, as I tried to explain before.

Thx.

|||

Thanks guys, I really appreciate your help. I'm good now.

Compare Time values of a datetime field

I have a table called 'JobSchedules' it contains datetime values for when to
start a job and when to end a job. The date part of the equation is
irrelevant as the schedule is meant to start and stop jobs daily.
How can I do a query to return jobs with a starttime = 9:30 a.m. (for
example) or where the time portion of getdate() is between the StartTime and
EndTime (time portions).
There has to be relatively simple method to do this right?You should make sure the DATE portion is always the same value.
'20050101' for example is Jan 01 of 2005. Then the only relevant part will
be the time and you can use = or Between and compare directly.
Andrew J. Kelly SQL MVP
"Roger Twomey" <rogerdev@.vnet.on.ca> wrote in message
news:OdXxi9zRFHA.3336@.TK2MSFTNGP10.phx.gbl...
>I have a table called 'JobSchedules' it contains datetime values for when
>to start a job and when to end a job. The date part of the equation is
>irrelevant as the schedule is meant to start and stop jobs daily.
> How can I do a query to return jobs with a starttime = 9:30 a.m. (for
> example) or where the time portion of getdate() is between the StartTime
> and EndTime (time portions).
> There has to be relatively simple method to do this right?
>|||>I have a table called 'JobSchedules' it contains datetime values for when
>to start a job and when to end a job. The date part of the equation is
>irrelevant as the schedule is meant to start and stop jobs daily.
Then why not always use the same date (e.g. 1900-01-01)?

> How can I do a query to return jobs with a starttime = 9:30 a.m. (for
> example) or where the time portion of getdate() is between the StartTime
> and EndTime (time portions).
> There has to be relatively simple method to do this right?
If the dates are all different, then with your existing data you will have
to do messy converts etc. One way would be:
SELECT columns FROM table WHERE CONVERT(CHAR(8), column, 108) = '09:30:00'
Not very efficient. I would recommend doing this:
UPDATE table SET column = CONVERT(CHAR(8), column, 108)
And updating the application(s) and/or stored procedure(s) to insert time
only. Then the dates will all be 1900-01-01. Now you can use an index, and
say:
DECLARE @.startTime SMALLDATETIME
SET @.startTime = '9:30'
SELECT columns FROM table WHERE starttime = @.startTime
(If you need date only for some other reason, e.g. indicating when the
recurring schedule started, then use a separate column.)|||Try,
...
where
getdate() between cast(convert(varchar(11), getdate(), 126) +
right(convert(varchar(50), StartTime, 126), 12) as datetime)
and cast(convert(varchar(11), getdate(), 126) + right(convert(varchar(50),
EndTime, 126), 12) as datetime);
No search-arguments in the expression, so do not expect and index s.
AMB
"Roger Twomey" wrote:

> I have a table called 'JobSchedules' it contains datetime values for when
to
> start a job and when to end a job. The date part of the equation is
> irrelevant as the schedule is meant to start and stop jobs daily.
> How can I do a query to return jobs with a starttime = 9:30 a.m. (for
> example) or where the time portion of getdate() is between the StartTime a
nd
> EndTime (time portions).
> There has to be relatively simple method to do this right?
>
>

Compare datetimes

Hi

I need to retrieve data from a table comparing datetimes. I mean I've a
table with a datetime data and I'll need to retrieve rows with date time
> now and date time < now + 5 minutes...

Is this posible ? Which is the best / easy way ?

Thanks in advance

J"Javier" <jleyba@.manresa.net> wrote in message
news:cu8frg$d8m$1@.news.ya.com...
> Hi
> I need to retrieve data from a table comparing datetimes. I mean I've a
> table with a datetime data and I'll need to retrieve rows with date time
> > now and date time < now + 5 minutes...
> Is this posible ? Which is the best / easy way ?
> Thanks in advance
> J

Check out DATEADD() in Books Online.

select col1, col2, ...
from dbo.MyTable
where dtcol > getdate()
and dtcol < dateadd(mi, 5, getdate())

Simon|||On Mon, 07 Feb 2005 20:34:05 +0100, Javier wrote:

>Hi
>I need to retrieve data from a table comparing datetimes. I mean I've a
>table with a datetime data and I'll need to retrieve rows with date time
> > now and date time < now + 5 minutes...
>Is this posible ? Which is the best / easy way ?

Hi Javier,

SELECT Column1, Column2, ...
FROM MyTable
WHERE MyDatetimeColumn > CURRENT_TIMESTAMP
AND MyDatetimeColumn < DATEADD(minute, 5, CURRENT_TIMESTAMP)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)