I have two tables that I am needing to link by the tables date field. In one table the date field is defined as varchar(23). The time for this field is always zeros.
Example: '2005-12-27 00:00:00.000'
The other table is defined as datetime, and it does have the date and time in this field.
Example: 2005-12-27 08:00:35.000
The problem i am having is
2005-12-27 00:00:00.000 does not = 2005-12-27 08:00:35.000.
Because I will never have more than one record on the same date I would like to be able to only compare the date. Example 2005-12-27 = 2005-12-27
Since the fields are 2 different field types, this is giving me a problem. Could someone please help. I have tried everything I know to do.
What I really need is the a way to format the datetime fields date into a string such as '2005-12-27'.
I remember at some point I had some problem with this issue and I couldn't find something that suites a newbie. The point that I was missing was that I couldn't pass the string representation of the date value to a query string. The key is to convert the string date to its equivalent datetime or smalldate . For example imagine you'd like to select all Employees who where born on a specific date stored in a variable called myDate: the BirthDay column in our Employee table has type smalldatetime ---------------------------------------------- string myDate = @"06/06/1978"; .... SqlCommand comm = new SqlCommand("SELECT * FROM EMPLOYEE WHERE [BirthDay] = Convert(smalldatetime,'" + myDate + "', 103))"; .... ---------------------------------------------- Make suer that you don't miss single quotes around value of myDate in your query string. For more info regrading the Convert function look at: http://msdn2.microsoft.com/en-us/library/ms187928.aspx
I'm having a problem with what should be a simple TSQL statement. I have a table which has a datetime field updated. After the update if I type
select * from patient_medication where rec_status_date = '11/10/2000' it returns the rows I want.(All the dates have a time of 00:00:00.000. But if I type select * from patient_medication where rec_status_date <> '11/10/2000' or select * from patient_medication where rec_status_date != '11/10/2000'
The rows that have a value are returned, but none of the null values are returned. Will nulls not work with this comparison? Thanks
In my current job I am learning MS SQL - while having to write valid SQL statements. I need some help designing a query that checks hire dates for establishing participation for (something).
So the statement establishing participation has to have a WHERE clause that will check HIRE_DATE and establish two different goals:
1. WHERE Hire_Date < OCTOBER 1 of this current year 2. WHERE Hire_Date < OCTOBER 1 of previous year
Different question now that I am thinking about it: some of these previous SQL statements have a @ symbol, what does that mean in SQL? Can't find an online resource that references that symbol Example: IF @Type IN ('Workflow','All')
HiI have a table that contains information that has start dates and enddates. They are stored in short date format.I have built a web page that initially returned all the information. Ithen want to return information spcific to todays date, where I useddim todDatetodDate = nowthen I didshTodDate = FormatDateTime(todDate, 2)which returned the date to a web page that was in the same format aswhat was in the access table.SELECT * from Events WHERE stDate = # ' & shTodDate & ' #;"This was fine beacuse it returned all events that started on that day.This doesn't help though because some events last a few days, so Itried the followingSELECT * FROM Events WHERE stDate >=#'&shTodDate&'# ANDendDate<=#'&shTodDate&'#;This still only returned events that started on the current day.Does anyone know how I can use <= or >= when comparing dates?ThankspuREp3s+
Hi all,I am trying to create a stored procedure that will check a date field.I want to check for records that are equal to or greater then 90 daysfrom the current date. I am trying to check this against a fieldcalled LastUpdate. Is there an easy way in SQL to do this?TIA
I am trying to use a date comparison in a statement using the year statement as well. Here is what I have:
Case [LastHireDate] When YEAR([LastHireDate]) < Year(@EndYearlyDate) then '12' When Month([LastHireDate]) = '1' then '12' When Month([LastHireDate]) = '2' then '11' When Month([LastHireDate]) = '3' then '10' When Month([LastHireDate]) = '4' then '9'
[Code] ....
When I am looking at it [LastHireDate] is showing that red line underneath. The < symbol has a red line and @EndYearlyDate has a red line. I can not seem to get them to clear and am, wondering what I am missing. When I execute the error comes up that it does not like the < sign in there.
I have a datetime variable coming from my ASP.NET application that hasa time portion. I give my users the option to perform an equals,greater than, less than, or between comparison. The trouble comes inthe way the application builds the criteria string. The WHERE clausepassed in is in the format, "(start_dt = '2005/05/16 07:00:00.000')".What I want to do is only compare the date portion of start_dt to thedate portion of the passed in time. Manipulating the start_dt with thebuilt-in SQL functions isn't a problem, but altering the date passed infrom the ASP.NET would be a massive framework change in the app.Is there any way to only compare the date portions of both the SQLfield and the passed in value?Thanks.
I need to take all records from table @A where ID = 1. Also i need to process the records with datewise from table @A. Here is the table structure
DECLARE @A TABLE (ID INT, ACCOUNT VARCHAR(10), EFFDT DATE) INSERT INTO @A VALUES (1,'AAA','2015-10-01') INSERT INTO @A VALUES (1,'BBB','2015-10-01') INSERT INTO @A VALUES (1,'CCC','2015-10-01') INSERT INTO @A VALUES (1,'AAA','2015-10-05') INSERT INTO @A VALUES (1,'DDD','2015-10-01') INSERT INTO @A VALUES (2,'AAA','2015-10-02') INSERT INTO @A VALUES (2,'BBB','2015-10-02') INSERT INTO @A VALUES (2,'CCC','2015-10-02') INSERT INTO @A VALUES (2,'DDD','2015-10-02')
[code]...
how to achieve this in SQL query, i cannot use CTE or temp table as i need to use this code in another tool, it has to be single query, can use subquery or join would be better.
I would like to return the nearest date of Table B in my table like for
ID W001 in table B should return ID A002 CreatedDatetime: 2014-06-03 20:05:48.000 ID W002 in table B should return ID A004 CreatedDatetime: 2014-06-04 01:05:48.000
I am new to the topic T-SQL. I am trying to use T-SQL to merge the content of two tables (table1 and table2) into one table making sure there are no duplication.
I wonder if any body can let me have a simple code.
Hey guys what would be the easiest way to create a report of value changes for particular records from one day to the next..... ? Any suggestions would be greatly appreciated....
Hi there! can anyone help me out??? I need to compare a date from the database and the system date which will be coded in a store procedure in SQL... HELP!!!!!
I have a table with a field with a bit datatype. When I execute the stored procedure line if @bitvalue = 1 begin ... and the value is passes as 0 the statements beneath the begin execute. What am I doing wrong?
Is there a way to compare two similar tables? I'm more interested in finding out if the data content is exactly the same or not between the two tables.
We are converting our project into new version. They have done lot of changes in new version including normalization/denormalization. I need to compare the old and new database. Do you have any standard script or procedure like SQL Compare software?. Let me know what are the possibilties we need to check. Your help appreciated. Thanks, Ravi
Assuming a table with a column defined char or varchar. I have a SQL query like this :
Select * from table1 where column1='Building'
It returns the same result that
Select * from table1 where column1='BUILDING'
It is my understanding SQL Server (verison 7 or 2000, I tried on both) is lower/capital insensitive by default when it is installed. If I want SQL Server to be case-sensitive with my char or varchar columns, where can I set it?
Is it at database level or server level I can find this setting . What is the setting that control it?
Best Regards, Alain Gagne, Lead DBA gagnea@msagroup.com
Morning! Folks, i want some links where i could find healthy stuff regarding New Features and Compliance levels, performance comparisons, TPC tests etc about SQL 2000 and Yukon specially. I've to submit a document regarding Top database features that shall be used for a Medical-Billing Software in plan.
In management studio, is there a way to compare databases side by side? Or would anyone know of any freeware out there that would do such a task? :beer:
I am doing this 2 queries, they get data from the same tables in a very different way. The new way is a lot faster but of course there are problems. There are some discrepancies in the results. Both queries produce results with more than 200.000 rows. Second one has ca. 100 rows less than the first one and some of the results are wrong (most are correct though).
My question is how do I find the differences. I did the left outer join when column a.result != b.result (lines are defined by date and ID so they should be unique).but the join gives me problematic results (some are not different at all, some differences are not in the result).so how do I find the differences between 2 queries, including missing results/rows?
I have an Employee table with 3000 records and an Excel file having themodified data of those emplyoees. Some of the data of Excel may be sameas that of table data but some may differ. EmpId is the unique field.Other than this field, other fields of Excel may have modified data.Ineed to compare the data from SQL Server table with Excel Data.I decided to write a VB Program having two recordsets,one for SQLServer and other for Excel and compare each field's value. If themodified value is found then update that to table. Is there any way tocompare in SQL Server itself?Madhivanan
I am looking for some papers/information that compare relationaldatabases such as oracle, mysql, sql server etc. I am particularlyinterested in their features such as locking mechanisms, integrityconstraints, views... Anyone know where I can find the information?Thanks,Susan
I'm in need of a sql query that I'm not sure is possible. Here is anexample of how it's laid out.employee ID Job class Last Change Date12345 x 2/1/200412345 y 1/15/200412345 z 1/1/2004We know that this person is in job class 'x' because it's the mostrecent change. Is there a way to write a query that will exclude thelines 'y' and 'z' because they are currently incorrect?I would appreciate any help I could get. Thanks
As many of you know I've been hunting down a SQL CE alternative / embedded DB that is easily portable among handhelds.
It turns out, that without paying for some other DB, there really aren't any good ones... Unless you go GPL, and the interpretation of GPL that most are using is that your code too is GPL even though you may only Dynamically Link to their app. I think this is likely wrong, but none the less I'm back here yet again.
What I'm looking for this time is a definition of how SQL Server CE is implemented, how it differs from a full blown DB, etc, so I can more thourally explain "why SQL CE" is an embedded DB, and how it isn't the full blown SQL Server that has a bad reputation with some.
My guess is:
SQL Server CE is implemented via DLL. There aren't sockets etc to allow you to connect, and it operates more like an advanced hash on the file system.
Please correct me if i'm wrong, or... send me a link where I can further research. Also, if you have another embedded DB as a suggestion, that's free of course, please point it my way.
I'm trying to do a comparison of 2 data sets. Basically what I want is: 'where event date from event number -24 is earlier than the event date for event number -13'
To get the eventdate for the eventno's, I have the following 2 queries:
select eventdate from caseevent, cases where eventno = -24
select eventdate from caseevent, cases where eventno = -13
So what i'm trying to say is: I want it so that the value of the first query is compared to be LESS than the value of the second query...
