How Can I Compare Datetime In A Query???
Jul 4, 2006
I have a table with the following columns
company_Id employee_id logon_time_id logoff_time start_valid_period end_valid_period
Employee's working time should only be counted if it is between start_valid_period and end_valid_period
So, if I have for employee1 from company1
logon_time_id = 04/07/2006 11:00
loggoff_time = 04/07/2006 12:20
start_valid_period = 04/07/2006 12:10
end_valid_period = 04/07/2006 12:30
I should consider 04/07/2006 12:10 as the initial datetime, 04/07/2006 12:20 as the final datetime, and count only 10min of work to him. In code:
if(logon_time_id < start_valid_period) initialDatetime = start_valid_period else initialDatetime = logon_time_id
if(logoff_time < end_valid_period) finalDatetime = logoff_time else finalDatetime = end_valid_period
Is there anyway I can do this in a query, without using a stored procedure with "ifs" and everything else?
Thank you!
View 8 Replies
ADVERTISEMENT
Nov 4, 2005
how can i compare date @date1, @date2, @date3I want to check whether @date2 <= @date1 AND @date1 < @date3 in terms of (comparing Year, Month, Day ONLY)Those date don't need to compare the time.Any idea?
View 1 Replies
View Related
Feb 2, 2006
guys this is kind of a biggie for me.
i've only used sql server to compare the Date Portion of date(s), not the whole string (mm/dd/yyyy hh:mi).
i have a table called Time_Check which has two fields with timestamps,
"TimeLastRun & TimeNextRun". i was trying to compare the Current TimeStamp that i pass from a vb.net form to a stored procedure and compare that DateTime to the TimeNextRun. I'd like to then Update the two Time Columns if rows are found. Should i maybe be using the DateDif Func if i know ahead of time how much time should be elapsed(say five minutes)?
Any Code help is appreciated
Create Procedure dbo.My_Time_Check
@myDate smalldatetime
as
select alertnumber,alertname,TimeLastRun,TimeNextRun
from time_check
where timeNextRun >= @myDate
--would like to update these two columns if rows are found
thanks again
this forum is the best
rik
View 5 Replies
View Related
Aug 2, 2007
I am trying to do a select where one set of date/time columns are greater than another. My date is stored in one column while the time is stored in another. Only the date portion of the date column is valid and only the time portion of the time column is valid.
Example my date column value is 8/1/2007 01:01:01 AM and my time column value is 1/1/2007 07:23:49 AM which in my system means the last update time was 8/1/2007 07:23:49 AM.
My select statment looks like this.
SELECT *
FROM CHANGE
WHERE
CONVERT(DATETIME( DATE_LAST_ALTERED, TIME_LAST_ALTERED)) < CONVERT(DATETIME(DATE_APPROVED,TIME_APPROVED))
I get an incorrect syntax near 'DATE_LAST_ALTERED'.
Am I totaly missing the point of DATETIME?
Matt
View 7 Replies
View Related
Aug 29, 2007
hi guyz i want to compare time from DateTime field i.e. i want to identify if the time is from 1pm to 2pm the do this else do......
select DATEPART(hour, loginTime) ......returns me the hour i can get the Hour part of the time but the prblem is how to identify it
whether it is less than 2:00:00 pm and greater than 1:00:00 pm i can do this task using at application level but i want this to b done at query level
any ideas??????????
View 2 Replies
View Related
Mar 14, 2008
Hi,
Can i compare date = null in stored procedure? Does this work? The syntax works but it never get into my if else statement? The weird thing here is it has been working and just this morning, it stopped. I don't know why? Could you please help? Thanks,
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[UpdateActiveStates1]
@PROVIDERID INT
AS
DECLARE @STARTDATE DATETIME
DECLARE @ENDDATE DATETIME
DECLARE @ACTIVE BIT
SET @ACTIVE = 0
SET @STARTDATE = (SELECT ProhibitionStartDate FROM Providers WHERE ProviderID=@PROVIDERID)
SET @ENDDATE = (SELECT ProhibitionEndDate FROM Providers WHERE ProviderID=@PROVIDERID)
IF ((@STARTDATE = NULL OR @STARTDATE = '1753-01-01') AND (@ENDDATE = NULL OR @ENDDATE = '1753-01-01'))
BEGIN
PRINT 'Setting Inactive due to NULL Start Date and NULL End Date'
SET @ACTIVE=0
END
ELSE IF (@STARTDATE != NULL AND @STARTDATE <= GETDATE())
BEGIN
IF (@ENDDATE = NULL)
BEGIN
PRINT 'Setting Active due to NON-NULL Start Date and NULL End Date'
SET @ACTIVE=1
END
ELSE IF (@ENDDATE >= GETDATE())
BEGIN
PRINT 'Setting Active due to NON-NULL Start Date and NON-EXPIRED End Date'
SET @ACTIVE=1
END
ELSE
BEGIN
PRINT 'Setting Inactive due to NON-NULL Start Date and EXPIRED End Date'
SET @ACTIVE=0
END
END
UPDATE Providers SET Active=@ACTIVE
WHERE ProviderID=@PROVIDERID
UPDATE Actions SET Active=@ACTIVE WHERE ProviderID=@PROVIDERID
View 2 Replies
View Related
Jul 30, 2007
in my SQL 2000
the column importDate contain Date as a vachar , type is 20060324225008 ( 2006 -year , 03-month, 24-day)
I want to compare this column with today's date, how to transform it?
how to return value 20060324 not 20060324225008?
thank you
View 3 Replies
View Related
Sep 26, 2007
Hi,
I have to compare a DATETIME Field with '1/1/1900 12:00:00 AM". Which is default DATE TIME Value in SQL Server.
I did compare like
TRADEAGREEMENTFROMDATE != (DT_DBTIMESTAMP)(DATEPART("mm",(DT_DBTIMESTAMP)"1/1/1900 12:00:00 AM"))
but (DT_DBTIMESTAMP)(DATEPART("mm",(DT_DBTIMESTAMP)"1/1/1900 12:00:00 AM")) returns "12/31/1800 12:00:00:AM"
Thanks,
Aravind
View 1 Replies
View Related
Nov 2, 2003
Hi,
I am using one datetime data type ( name: date_added ) and getdate() as default value. I want to display only those records added today. How I can compare current date with date_added.
Thanks
Manoj
View 1 Replies
View Related
May 19, 2008
hai friends,
iam doing a project in .net and using sql server.
i need to compare only month and year part in datetime type to retrive data.
1)retrive unique year and its months available in the database.
like may 2008
apr 2008
mar 2007
View 3 Replies
View Related
Oct 31, 2015
If we want to compare datetime vs a specific time what should we do?
i.e. startdatetime specific time
2015-10-12 00:03:19:020 16:23:00
I want to compare just time in startdatetime coloum vs a specific time i.e. 16:23 .
I convert startdatetime to (DT_DBTIME) after that i want to calculate just time difference. how can i do it?
i.e i want to calculate this two (DT_DBTIME) columns without any date ==> (16:23:00)-(00:03:19)
View 6 Replies
View Related
Mar 17, 2007
Hey :)I'm facing a lot of troubles trying to create a new pause/break-system. Right now i'm building up the query that counts how many records that is inside 2 fields. Let me first show you my table:
ID (int) | stamp_start (Type: DateTime) | stamp_end (Type: DateTime) | Username (varchar)0 | 17-03-07 12:00:00 | 17-03-07 12:30:00 | Hovgaard
The client will enter a start time and a end time and this query should then count how many records that are inside this periode of time.
Example: The client enter starttime: 12:05 and endtime: 12:35.The query shall then return 1 record found. The same thing if the user enters 12:20 and 12:50.My current query looks like this:SELECT COUNT(ID) AS Expr1 FROM table WHERE (start_stamp <= @pausetime_start) AND (end_stamp >= @pausetime_end)But this will only count if I enter the exact same times as the one inside the table.Any ideas how I can figure this out?Thanks for your time so far :)/Jonas Hovgaard - Denmark
View 2 Replies
View Related
Aug 4, 1999
Hi,
I am trying to write a query to compare the same column in each table with "not equal" expression.
My query is like this:
select tableOne.empl_ser_no from tableOne, tableTwo
where tableOnel.empl_ser_no <>(select empl_ser_no from tableTwo)
I am getting the following message from SQL Server:
Msg 512, Level 16, State 1
Subquery returned more than 1 value. This is illegal when the subquery follows =, !=, <, <= , >, >=, or when the subquery is used as an expression.
Command has been aborted.
View 1 Replies
View Related
Dec 26, 2013
I am new to writing the queries in SQL.I want to write a query which will compare the data of two tables which are resides in DEV server and PROD Server.For the conncetivity purpose we are creating the DB link between DEV and PROD server.query to compare the data of table in DEV and table in PROD
View 2 Replies
View Related
Nov 4, 2005
I have a database I want to query. It is a medical billing system. I basically want to compare a list of Names and Birthdates in an Excel spreadsheet to the table of patients in the database that have that insurance type and return only the rows from the spreadsheet that don't exist in the database.
This is an eligibility list, so we need to find those that don't have this insurance set up for them in our billing system and update their records.
We will probably want to do the update manually so I don't mess it up too bad , but would like to zero in on who needs the updates rather than having to look at every single record.
Any ideas on how to approach this?
Would this be a join, or would I need to do a select from table 1 where pt/insurance not in select pt/ins from table 2?
View 1 Replies
View Related
Feb 13, 2007
Hi I wanna put string in query "where" part.For example,$sql="select VEHICLEFROM databaseWHERE MECHANIC =BRIAN";like above, "mechanic" column is filled with strings. Then how can Iwrite "where" part?Above query does not work.Thanks.
View 2 Replies
View Related
Jun 9, 2004
I am trying to QA data being put into a SQL database by an outside source (from Excel) and therefore need to compare two (for the sake of simplicity) tables within a database to one another.
The two tables should contain the same data, and the QA process is meant to find and report any discrepancies. The column names are slightly different.
My question then is, is it possible to write a simple SQL query which will compare the data from the two tables and select only those rows where the data in any given column does not match? My data is mostly text, not numerical.
I'm very new at using SQL and my knowledge of the query syntax is very basic.
Thanks for any help in advance!
~Lacy
View 2 Replies
View Related
Jul 23, 2005
Okay all I have a problem. I have two list of adresses and phonenumbers. I do not have control over the contents of the lists The onlyunique field between the two is the phone number. I need to be able toinner join the two lists on phone number.This would normally be straigt forward but the problem is that they areformated different and one of them does't even have a control on theformating.*Phone numbers are US phone numbers only.The one list (table) that does have a control uses this formatAAA3334444where AAA is the area code333 is the 3 digit prefix4444 is the four digit suffixthe second list (table) does not have any standardized formating andcan be filled with extraneous spaces, parentheses and dashes not tomention the leading 1 in some instances.I thought that I could do some kind of regular expression to do acomparison but I havn't as yet found a good resource to tell me how todo it or if it is even possible. Or maybe break up the one I know hasa standardized format into something like this:'%AAA%333%4444%' and doing my comparison that way. however It is veryimportant that only those list items in both list that are truly thesame place be listed.suggestions and solutions are apreciated
View 3 Replies
View Related
Feb 22, 2007
Can We Compare alias of column(Derived column) within the same query??
Ex:
Select (abc+50)*100 as 'WXY' from XYZ where WXY>150
.....
I cant execute such statement ... Can anyone help me how to comapre the alias within the same query..?
View 3 Replies
View Related
Jul 31, 2007
dear all
i need to write SQL Query that compares 2 tables as folows:
lets say i have:
table A with A.A, A.B, A.C columns (Table A with Columns A,B,C)
table B with B.A, B.B, B.C columns (Table B with Columns A,B,C)
i need to compare and to mark (get in result Query) each row lets say in table B that was changed from table A
For Example
Table A
A B C
12 15 hello
15 17 adv
asd 19 23
14 rer 89
Table B
A B C
12 15 hello
15 19 adv
*** 19 23
14 rer 89
the result is the records which was changed from A to B
A B C
15 19 adv
*** 19 23
Thnks alot!
View 5 Replies
View Related
Sep 17, 2015
Table 1 has "Gender" field with "Male" and "Female" in it, table 2 has "Gender" field with "M" and "F" in it. a query to compare data and list the differences.
View 4 Replies
View Related
Apr 24, 2015
I have a query that ranks. Once I get the ranked fields is there a way to compare the 2 total_income fields?
Here's the query:
select * from
(
select cardholderid, appcnum, total_income ,Rank() over (PARTITION BY A.APPCNUM
ORDER BY A.EFFSTARTDATE DESC) as Rank
from
TBL_EPIC_BILLSTATUS A
) tmp
where Rank in (2,3) and CARDHOLDERID = '704355'
--------------and rank ((2),total_income) <> rank (3),total_income))
Looking to do something like this to see if the income is different
View 17 Replies
View Related
Jul 23, 2005
I am debugging one of our programs and ran the fix in Test. I would liketo compare table 1 between Production and Test. I want the query to outputcolumn 1 if Production <> Test output.What is the best way to achieve this?jeff--Message posted via http://www.sqlmonster.com
View 2 Replies
View Related
Nov 6, 2007
Hi All ,
I am having a table which contain data in below form
id startvalue endvalue
1 12:00a 12:29a
2 12:30a 12:59a
3 1:00a 1:29a
4 1:00p 1:29p
5 2:00a 2:29a
Through SQL query I want to select the id column value for the row returned for the following query
select id from mytable where '1:24a' >= startvalue and '1:24a' <= endvalue
Idea is I will pass a literal value that lies between the startvalue (nvarchar column) and endvalue (nvarchar column) to fetch the id of the row returned, which should be 3 But when I run the query it returns me 2 rows for id 3 and 4.
I believe this is because I am trying to compare nvarchar columns with ">=" and "<=" operators.
Can anyone suggest how i can select the correct value or how to do this.
Thanks,
Ashish
View 2 Replies
View Related
Nov 18, 2015
There are two tables testmaster and testdetail. If the value of Price for a particular ID in testdetail is more than the threshold value defined in testmaster, the output should have a new column with value as 'High Value', if the value is less than the threshold the new output should be 'Low Value' other wise 'Ignore'
Example: for ID=3, threshold is defined as 40% in testmaster table, but on 11/12/2015 the new price is 100 which 100% more than the previous value, so the status is High Value as shown below.
ID Date
Price Status
1 11/12/2015 100 Low Value
2 11/12/2015 160 Ignore
3 11/12/2015 100 High Value
create table testmaster
(
ID int,
Threshold int
)
create table testdetail
(
ID int,
Date varchar(20),
Price float
)
[Code] ...
View 15 Replies
View Related
Jun 30, 2006
Hi,
This is my code:
SqlCommand getPreviousDateCmd = new SqlCommand("SELECT Top 1 Open_date FROM Counter WHERE (Open_date <= @todays_date) ORDER BY Open_date DESC", myConnection); //Get previous date getPreviousDateCmd.Parameters.AddWithValue("@todays_date", DateTime.Now.ToString("dd/MM/yyyy")); DateTime previousDateTime = Convert.ToDateTime(getPreviousDateCmd.ExecuteScalar().ToString());
//previousDate = previousDateTime.ToString("dd/MM/yyyy"); LabelToday.Text = previousDateTime.ToString("dd/MM/yyyy"); //If previous date is same as today's date (Get only date and year)
I get a huge unstoppable(?) error message when it converts STRING to DATETIME. What I want to do is get DATETIME object and change the format to "dd/MM/yyyy"
Could someone give me some ideas? Thankx!
View 1 Replies
View Related
Sep 13, 2007
Hi
I am trying to write a query involve parameters. For example, the query:
Select * from myTable
where myDateTime=@dt;
If I run the query, I was asked to enter value for the parameter. The query can be generated, however I can't save it, the error message says: Must declare the variable @dt. When I tried to declare it, the system doesn't support it. I am using SQL Server Managerment Studio 2005.
I also tried the query without the parameter:
Select * from myTable
where myDateTime=31/07/2007;
But it didn't return record for any datetime format.
Could anyone help please? I just want to get some records filtered by a certain DateTime.
Claire
View 2 Replies
View Related
Jan 16, 2008
Hey gang, I need a little help creating a Query. I have a table storing info about visitors to our company. The name of the table is "visitors" and it has an "arrivalDate" field and a "departureDate" field. Both of these fields are of DateTime data type and both fields are populated prior to the visitors arrival, neither field will accept a null value. I need to run a report each day to see which visitors are currently on site. I am by no means a SQL expert and I am drawing a blank on how to extract this info??
Entry Example:
name: John DoearrivalDate: 1/16/08departureDate: 1/20/08So every morning between 1/16/08 and 1/20/08 John Doe must show up on the report
I was trying to do something like this with my code (using todays date):
SELECT name FROM visitors WHERE ((MONTH(arrivalDate) = 1 AND DAY(arrivalDate) >= 15 AND YEAR(arrivalDate) = 2008) AND (MONTH(departureDate) ?????
View 5 Replies
View Related
Aug 7, 2005
my database contains 1 field with "datetime" in sql serverhw can i query the database to just compare the date section of that field ??? and not the time
View 2 Replies
View Related
Mar 6, 2005
i am trying to query a datetime column in a db.
e.g. 3/7/2005 4:24:01 AM
My query is below :-
--
select a.date, b.useruri as 'FROM', c.useruri as 'TO',
a.body as 'MESSAGE' from messages as a
inner join
users as b
on a.fromid = b.userid
inner join
users as c
on a.toid = c.userid
where a.date like '%2005-03-01%'
order by a.date
View 3 Replies
View Related
Sep 18, 2007
How to display records between datetime field??
View 7 Replies
View Related
Jan 16, 2008
Hey gang, I need a little help creating a Query. I have a table storing info about visitors to our company. The name of the table is "visitors" and it has an "arrivalDate" field and a "departureDate" field. Both of these fields are of DateTime data type. I need to run a report each day to see which visitors are currently on site. For a partial example, I will use today's date of 1/16/08:
SELECT name FROM visitors WHERE ((MONTH(arrivalDate) = 1 AND DAY(arrivalDate) >= 15 AND YEAR(arrivalDate) = 2008) AND
(MONTH(departureDate) ?????
It is the second part of the query that I am having trouble with. Am I going about this all wrong? Is there a way to extract this information when all I know is the persons arrival date, departure date, and the current days date? Any help would be greatly appreciated.
View 5 Replies
View Related
Feb 28, 2008
i have select query :
select indxid, indxname, createddate from outmailtab
where indxname like 'update%'
indxid indxname createddate
----------------------------------------------------------------------
84627 update_sept2007 9/26/2007 10:13:46 AM 2007-09-26 10:14:11.213
84652 update_sept2007 9/26/2007 11:20:29 AM 2007-09-26 11:21:15.947
84675 update_sept2007 9/26/2007 1:18:07 PM 2007-09-26 13:18:47.237
150326 update_Jan2008 1/22/2008 12:32:25 PM 2008-01-22 12:40:48.490
150430 update_Jan2008 1/22/2008 3:33:43 PM 2008-01-22 15:41:24.523
i want output like max row ...
indxid indxname createddate
----------------------------------------------------------------------
84675 update_sept2007 9/26/2007 1:18:07 PM 2007-09-26 13:18:47.237
150430 update_Jan2008 1/22/2008 3:33:43 PM 2008-01-22 15:41:24.523
can anyone tell me how to get it?
i tried like:
select indxid, indxname, max(createddate) from outmailtab
where indxname like 'update%'
group by indxid, indxname
but still its getting me same results..i think because of datetime..date is same for different indxid but time is different for all...so can anyone help me to figure it out with datetime function..
thanks.
View 14 Replies
View Related
