Compare DateTime Values

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


ADVERTISEMENT

Compare Incoming Input File Row Values With Database Row Values In SSIS

Jan 23, 2008

Hi All,

I receive the input file with some 100 columns and some 20k+ rows and I want to check the incoming input row is existed in the database or not based on 2 key columns. If the row is existed then I need to check all the columns (nearly 100 columns) values in input and the database are equal or not. If both are equal I need to treat them seperately if not there is a seperate logic. How Can I do that check for each row and for each column?

Basically the algorithm is like this, if the input file row is not existed in the database then treat that as new row else if the input row is existed in the database then check all the columns are equal or not. If all the columns are equal then treat that as existing row and do nothing else if some columns are not equal then treat this row seperately.

I found some thing to achieve the above thing.
1. Take the input row and check in the database.
2. If the row is not found in the database then treat it as new row.
3. If row is found in the database then
a) Take the source row and prepare a concatenated string for all the columns
b) Take the database row and prepare a concatenated string for all the columns
c) Find out the hash code for the 2 strings and then compare hash codes for equal.

The disadvantage of this is running a loop 2*m*n times where m is the number of rows and n is the number of columns. It should be done 2 times for input file row and database row.

Can anybody suggest a good method to do this?

What does the function "GetHashCode" for InputBuffer in method "Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)" will do?
Will it generates hash code based on all the columns values?

Pls clarify.

Regards
Venkat.

View 1 Replies View Related

Millisecond Values Missing When Inserting Datetime Into Datetime Column Of Sql Server

Jul 9, 2007

Hi,
I'm inserting a datetime values into sql server 2000 from c#

SQL server table details
Table nameate_test
columnname datatype
No int
date_t DateTime

C# coding
SqlConnection connectionToDatabase = new SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=testdb;Integrated Security=SSPI");
connectionToDatabase.Open();
DataTable dt1 = new DataTable();
dt1.Columns.Add("no",typeof(System.Int16));
dt1.Columns.Add("date_t", typeof(System.DateTime));
DataRow dr = dt1.NewRow();
dr["no"] = 1;
dr["date_t"] = DateTime.Now;
dt1.Rows.Add(dr);
for(int i=0;i<dt1.Rows.Count;i++)
{
string str=dt1.Rows["no"].ToString();
DateTime dt=(DateTime)dt1.Rows["date_t"];
string insertQuery = "insert into date_test values(" + str + ",'" + dt + "')";
SqlCommand cmd = new SqlCommand(insertQuery, connectionToDatabase);
cmd.ExecuteNonQuery();
MessageBox.Show("saved");
}
When I run the above code, data is inserted into the table
The value in the date_t column is 2007-07-09 22:10:11 000.The milliseconds value is always 000 only.I need the millisecond values also in date_t column.
Is there any conversion needed for millisecond values?

thanks,
Mani

View 3 Replies View Related

Sql Datetime Compare

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

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 View Related

DATETIME Compare Issue

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

How To Compare Time......... Using DateTime Field

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

Compare Datetime In Stored Procedure

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

How To Compare Vachar Which Type :20060324225008 With Datetime?

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

Conditional Split - Compare DATETIME With Constant

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

How To Compare Current Date With Sql Db Datetime Data Type

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

Compare Only Month And Year Part In Datetime Type

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

Null Values For Datetime Values Converted To '0001-01-01'

Mar 29, 2006

Hi

can somebody explain me how I can assign a NULL value to a datetime type field in the script transformation editor in a data flow task.
In the script hereunder, Row.Datum1_IsNull is true, but still Row.OutputDatum1 will be assigned a value '0001-01-01' which generates an error (not a valid datetime). All alternatives known to me (CDate("") or Convert.ToDateTime("") or Convert.ToDateTime(System.DBNull.Value)) were not successful.
Leaving out the ELSE clause generates following error: Error: Year, Month, and Day parameters describe an un-representable DateTime.




If Not Row.Datum1_IsNull Then

Row.OutputDatum1 = Row.Datum1

Else

Row.OutputDatum1 = CDate(System.Convert.DBNull)

End If



Any help welcome.

View 1 Replies View Related

Integration Services :: Compare Datetime With Specific Time In SSIS

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

Check Constraint - Compare Sum Of Two Values In One Table Against Values Located In Another Table

Jul 26, 2014

I am relatively new to SQL and as a project I have been asked to create the SQL for a simple database to record train details. I want to implement a check constraint which will prevent data from being inserted into a table if the weight of the train is more than the maximum towing weight of the locomotive. FOr instance, I need to add the unladen weight and maximum capacity of each wagon (located in the wagon type table) and compare it against the locomotive maximum pulling weight (the locomotive class table). I have the following SQL but it will not work:

check((select SUM(fwt.unladen_weight+fwt.maximum_payload) from
hauls as h,freight_wagon as fw,freight_wagon_type as fwt,train as t where
h.freight_wagon_serial_number = fw.freight_wagon_serial_number and
fw.freight_wagon_type = fwt.freight_wagon_type and
h.train_number = t.train_number) <
(select lc.maximum_towing_weight from locomotive_class as lc,locomotive as l,train as t where
lc.locomotive_class = l.locomotive_class and l.locomotive_serial_number = t.locomotive_serial_number))

The hauls table is where the constraint has been placed and is the intermediary table between train and freight wagon.

I may not have explained this very well; but in short, i need to compare the sum of two values in one table against a values located in another table...At present I keep getting a message telling me the sub query cannot return more than one row.

View 2 Replies View Related

How To Compare Int Values In VB.net?

Nov 11, 2006

Here is my statement:
Dim MyCommand As SqlDataAdapter
MyCommand = new SqlDataAdapter("select * from Request T1, Users T2 where RunID= "+ intRun_Num +" and T1.UserID = T2.UserID ORDER By Username", MyConnection)

I'm getting error saying:
Input string was not in a correct format.

the problem is the intRunNum. When I put intRunNum.toString(), there is no error but no results are coming up.

How do I get past this?

View 3 Replies View Related

Compare Row Values

Oct 22, 2007

hello there,

i have a matrix which displays data for different products.i have grouped them by year and month.now i want to compare each month to the previous month and show an indicator that this month the sales was down or up.for that i need to compare a month with the previous month.how can i do this in reporting service.please help.

cheers
zolf

View 1 Replies View Related

Compare Row Values

Oct 22, 2007

hello there,

i have a matrix which displays data for different products.i have grouped them by year and month.now i want to compare each month to the previous month and show an indicator that this month the sales was down or up.for that i need to compare a month with the previous month.how can i do this in reporting service.please help.

cheers
zolf

View 2 Replies View Related

How Do You Compare Date Values

Aug 2, 2004

I'm stuck trying to compare date values from users selection against a database. I also need to add a condition statement on if the results return no match. How would I do that?

Also, I'm not even sure if my SELECT statement is right. The user will hit a date that will be in format m/d/yyyy and that will be compared to see if it exist in the database. The datebase column "date_created" is default with date/time( 8 ). But when I compare the to together, nothing returns back. The page loads successfully, but no results from the database.

Here is my code:
dim conpubs as sqlconnection
dim cmdSelectAuthors as sqlcommand
dim dtrAuthors As sqlreader

conpubs = New sqlconnection(configurationsettings.appSettings("STD"))
conpubs.open()
cmdSelectAuthors = New sqlcommand("Select * From HotNews WHERE date_created=" & y, conpubs)
dtrAuthors = cmdSelectAuthors.ExecuteReader()
Response.Write("<table><tr><td>")

While dtrAuthors.Read()
Response.Write(dtrAuthors("title") & "<br>")
End While

Response.Write("</td></tr></table>")

dtrAuthors.close()
conPubs.close()

View 1 Replies View Related

How Do You Compare Date Values

Aug 2, 2004

I'm stuck trying to compare date values from users selection against a database. I also need to add a condition statement on if the results return no match. How would I do that?

Also, I'm not even sure if my SELECT statement is right. The user will hit a date that will be in format m/d/yyyy and that will be compared to see if it exist in the database. The datebase column "date_created" is default with date/time( 8 ). But when I compare the to together, nothing returns back. The page loads successfully, but no results from the database.

Here is my code:
dim conpubs as sqlconnection
dim cmdSelectAuthors as sqlcommand
dim dtrAuthors As sqlreader

conpubs = New sqlconnection(configurationsettings.appSettings("STD"))
conpubs.open()
cmdSelectAuthors = New sqlcommand("Select * From HotNews WHERE date_created=" & y, conpubs)
dtrAuthors = cmdSelectAuthors.ExecuteReader()
Response.Write("<table><tr><td>")

While dtrAuthors.Read()
Response.Write(dtrAuthors("title") & "<br>")
End While

Response.Write("</td></tr></table>")

dtrAuthors.close()
conPubs.close()

View 1 Replies View Related

Compare Values From Two Columns?

Sep 20, 2013

I am trying to compare values from two columns but I don't know what I am doing, I tried select custom5,skipcount substr( custom5, 1, 4) as 'mynumber' from songs where custom5 > '0000%' where skipcount > cast( substr( custom5, 1, 4) as int ); But that is obviously wrong?

View 4 Replies View Related

How Compare Values Of 2 Different Rows In Same Tbl

Nov 17, 2005

Hi

i have this variable called order1 in the table called datetable and i want to display out the duplicate orders in sql. how do i do that? do i need to use group by ?

thanks in advance.! any help?

well i actually get the answers by doin this...
select order1 from datetable group by order1 having count(*) > 1

however now i want to compare the values date1 in the first duplicate orders to the second duplicate order...like compare aaaa of the first row and aaaa of the second row ...how to do that? i need to compare the date1 in minutes..i only wan duplicate orders with minutes difference less than 30mins
eg

id Order1 date1
1 aaaaa 20051001 12:45
2 aaaaa 20051001 12:10
3 bbbbb 20031002 15:05
4 bbbbb 20031002 15:50

View 9 Replies View Related

Compare Values In Result Set.

Sep 11, 2007

This is my first post and I am probably not using the correct terminology but I will try. I want to compare the value of one of my fields in a result set to the value of the same column/field, but from the previous row in the result set. I am trying to identify the row in the result set when the value in a specified column changes value. I want to identify this row by placing a 1 in the last column. If that value did not change, then place a 0 in the last column.
I am not sure if this will make sense to anyone out there. Please let me know if more clarification is needed and I will try to provide it. Thanks for all your help in advance.

Don

View 2 Replies View Related

Compare 2 Values In Same Solumn

Jun 25, 2007

I am trying to compare the last two values in the same column of atable. First of all, I have a column titled Row_Index that uses anindex which starts at 1 and increments by 1. What I am trying to dois compare the values in the column 'Shift_Date' for the maximum valueof Row_Index and the (maximum value - 1) of Row_Index. I've beentrying to declare two strings, setting each string equal to one of myvalues, and then comparing the strings. But this doesn't seam to beworking right. I'm not sure I am even declaring the stringscorrectly. Does anyone know how I can do this. A sample of what oneof my values looks like in Shift_Date is ' 6/25/2007'.

View 3 Replies View Related

How To Compare Values In Different Rows?

Jul 30, 2007

Hi

I€™ve a table like this (in SQL Server 2000).


v1 v2
2 8
7 10
11 15
13 17

v1 value of a row must be grater than v2 value of previous row, other wise I need to display that row, in above example, I need to display 2nd & 4th rows.

please advise

Thanks

View 9 Replies View Related

Compare Values In A Column

Feb 17, 2008

I have a table with one column. The values can be Y or N. How do I compare the 2 values and return a result depending on the comparison. For example, if I my column data looks like this.

Y
N

I need to compare them and if they match return a specific value and if they are different return a specific value.

View 12 Replies View Related

Compare Data Values Of 2 Tables

Dec 4, 2014

I have 2 databases( "A" + "B") with identical number of tables and identical number of records for each table. There are also identical number of fields pre record per table. Table A has had the sensitive data within the fields scrambled.

I need to know if there is a way to read down each DB table by table, record by record, field by field and compare the data values. If they are different I need to output the Field name, the data value and the Table name from the Scrambled Table (lets say its "A").

View 1 Replies View Related

Compare Values Of Row With Same ID - Check If Changed

Jun 4, 2014

I'm pretty new to sql server and now I need to create a script that:

Compares the values of the row with the same id as the row with the update table(which has updated values in it) in which the price, description or replacement part have been changed.

If they did change they should be updated and if they haven't been changed nothing should happen...

View 2 Replies View Related

Compare Values In Consecutive Rows

May 8, 2008

I have the following variables VehicleID, TransactDate, TransactTime, OdometerReading, TransactCity, TransactState.

VehicleID is the unique vehicle ID, OdometerReading is the Odometer Reading, and the others are information related to the transaction time and location of the fuel card (similar to a credit card).

The records will be first grouped and sorted by VehicleID, TransactDate, TransactTime and OdometerReading. Then all records where the Vehicle ID and TransactDate is same for consecutive rows, AND TransactCity or TransactState are different for consecutive rows should be printed.

I also would like to add two derived variables.

1. Miles will be a derived variable that is the difference between consecutive odometer readings for the same Vehicle ID.

2. TimeDiff will be the second derived variable that will categorize the time difference for a particular vehicle on the same day.

My report should look like:

VehID TrDt TrTime TimeDiff Odometer Miles TrCity TrState
1296 1/30/2008 08:22:42 0:00:00 18301 000 Omaha NE
1296 1/30/2008 15:22:46 7:00:04 18560 259 KEARNEY NE

Can someone please help me here?

Thanks,
Romakanta

View 2 Replies View Related

How To Select A Column To Compare Two Values In It

Apr 24, 2015

How to select a column to compare two values in it with AND

Select distinct c.reg#, a.fname, a.lname, to_char(b.L#) as "L"
from employee a, driver b, trip c
where a.e#=b.e#
and b.l#=c.l#
and (c.reg#='PKR3344'AND c.reg#='WWF1155')
order by c.reg#;

error no rows selected..I want c.reg# for the two values inclusive

View 4 Replies View Related

Compare Based On Table Values.

Sep 10, 2007

HiBelow is a table with sample data to explain what I want to achieve.trackID member markerallele1------- ------ -------------------------------------------------------------------------------------------------------4734 4577 01-D8S1179 13.54734 4577 02-D21S11 12.64734 4577 03-D7S820 2.04734 4577 04-CSF1PO 3.04734 4577 06-TH01 4.04734 4577 07-D13S317 5.04734 4577 08-D16S539 9.04734 4577 11-vWA 6.04734 4577 12-TPOX 7.04734 4577 13-D18S51 1.04734 4578 01-D8S1179 13.04734 4578 02-D21S11 12.64734 4578 03-D7S820 2.04734 4578 04-CSF1PO 4.04734 4578 06-TH01 3.04734 4578 07-D13S317 5.04734 4578 08-D16S539 9.04734 4578 11-vWA 7.04734 4578 12-TPOX 6.04734 4578 13-D18S51 1.0Okay I am trying to compare allele1 values for differences for two orthree different member having the same trackID. The list of markerswould always be same for all members belonging to a particulartrackID.So in the above sample data. The trackID is same which is 4734. Thereare two members 4577 and 4578. The markers are same for each membersbut the corresponding allele1 values are different.I want to compare these two and state whether they are same or not. Iwould like to do this on a form containing subforms.Feel free to ask for clarification.Thank you.

View 2 Replies View Related

How To Compare 2 Values In Different Table And Sources?

Aug 1, 2007



I got 1 xml source and 1 sql DB table..
Eg: Contact table in xml contains

Email
--------
bbb@yaho.com
aaa@yahoo.com
ccc@yahoo.com

then COntact table in SQL server contains

aaa@yahoo.com
ccc@yahoo.com
zzz@yahoo.com

i want to compare the two table if got same value update the row..

how to compare it in SSIS

thx

View 4 Replies View Related

Compare Rows To Determine If Column Values Are Different?

Mar 2, 2011

I need to determine if a value for a group of rows is the same or different and I am not sure where to begin. I am fairly new to writing T-Sql code. I am working with SQL Server 2005. This is a work assignment; I am not a student.

I have two tables: One for Course and another for CourseDays. As the Course table implies, it lists courses for a school. The CourseDays tables has a row for each day a course is held. For instance, one course maybe held on Monday, Wednesday, and Friday, so there would be three rows, one row for each day. A given course could be held in a different room for each day. I am trying to write a query that returns one row for each course, and if the room number is the same for each day the class is held, return that room number in the row, else return 'Various'.

Course table:
CourseID, Desc
1 English
2 History
3 Science

CourseDays table:
CourseID, Day, RoomID
1 M 320
1 W 445
1 F 680
2 T 123
2 Th 123
3 M 514
3 T 514
3 W 521
3 Th 901
3 F 521

Expected results:
CourseID, Desc, RoomID
1 English Various
2 History 123
3 Science Various

I simplified the example, as there are about 30 columns in the table and about half a million rows.

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved