Error In Query--datetime Related

May 21, 2008

Hi y'all I have a problem in my query.
This is my query
SELECT     dbo.BOS_GL_Workplace.WpszState, Emp2.szName, Emp1.szName AS szEmpName, Emp1.szEmployeeId,
                    dbo.BOS_PI_Division.szName AS Divisi, dbo.BOS_PI_Team.szDescription,dbo.BOS_SD_Route.szRouteId, dbo.BOS_SD_Route.szDescription, dbo.BOS_SD_Route.szScheduleId,
                    dbo.BOS_SD_RouteItem.szCustId, dbo.BOS_SD_Route.szOpUserId,dbo.BOS_AR_Customer.szCustId ,dbo.BOS_AR_Customer.szName, dbo.BOS_AR_Customer.CustszAddress_1 AS AlamatKirim, dbo.BOS_AR_Customer.szTaxId,
                    dbo.BOS_AR_Customer.CustszAddress_1 AS AlamatTagih, dbo.BOS_AR_Customer.CustszCity, dbo.BOS_AR_Customer.CustszZipCode,
                    dbo.BOS_AR_Customer.szStatus, dbo.BOS_AR_CustSales.szPaymentTermId, dbo.BOS_AR_CustSales.decCreditLimit,BOS_AR_Customer.dtmStart,BOS_AR_Customer.dtmLastUpdated
                    FROM         dbo.BOS_PI_Employee Emp1 LEFT OUTER JOIN
                    BOS_PI_Employee Emp2 on Emp1.szSupervisorID = Emp2.szEmployeeID Left outer join
                    dbo.BOS_GL_Workplace ON Emp1.szWorkplaceId = dbo.BOS_GL_Workplace.szWorkplaceId LEFT OUTER JOIN
                    dbo.BOS_PI_Division ON Emp1.szDivisionId = dbo.BOS_PI_Division.szDivisionId LEFT OUTER JOIN
                    dbo.BOS_PI_Team ON Emp1.szTeamId = dbo.BOS_PI_Team.szTeamId LEFT OUTER JOIN
                    BOS_SD_Route on Emp1.szEmployeeId = BOS_SD_route.szOPuserId LEFT OUTER JOIN
                    BOS_SD_RouteItem on BOS_SD_Route.szRouteId = BOS_SD_RouteItem.szRouteID LEFT OUTER JOIN
                    BOS_AR_Customer on BOS_SD_RouteItem.szCustID = BOS_AR_Customer.szCUstID LEFT OUTER JOIN
                    BOS_AR_CustSales on BOS_AR_Customer .szCustId = BOS_AR_CustSales.szCustID
When I run that query, it was working fine the result was correct. Then I tried to add the where clause, like this:
WHERE BOS_AR_Customer.dtmLastUpdated >= '8/1/2006' AND BOS_AR_Customer.dtmLastUpdated <='9/21/2007'
And it was also working fine, the result was also correct--pay attention dudes the date format is mm/dd/yyyy
Then I try to change the date format to dd/mm/yyyy in the where clause like this (because later I realized that, this is the query that gonna be used):
WHERE BOS_AR_Customer.dtmLastUpdated >= '1/8/2006' AND BOS_AR_Customer.dtmLastUpdated <='21/9/2007'
The result was an error message saying:
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
Can you tell me whats wrong, please. I appreciate anykinds of help guys, thanks.
 
FYI : I use SQL SERVER 2000
 
Best Regards. 

 

View 1 Replies


ADVERTISEMENT

Error Related To IF Statement In Query

Jul 6, 2005

I'm having trouble getting a query to run that contains an IF statement:
*****************************************
DECLARE @lid int, @ptf varchar(50)
SET @lid = 3
set @ptf = '05-00005'

SELECT
tblInfoData.PTFileNumber AS [PT File Number],

IF @lid > 3
BEGIN
tblInfoData.OrigGrantor AS [Original Grantor],
END


tblPropertyData.propStreet AS [Property Address],
tblPropertyData.propCity AS City,
tblPropertyData.propState AS State,
tblPropertyData.propZip AS Zip,
tblSaleData.OrigSaleDate AS [Original Sale Date],
tblInfoData.Status AS Status
FROM tblInfoData INNER JOIN
tblPropertyData ON tblInfoData.PTFileNumber = tblPropertyData.PTFileNumber INNER JOIN
tblSaleData ON tblInfoData.PTFileNumber = tblSaleData.PTFileNumber
WHERE CONTAINS(tblInfoData.PTFileNumber, @ptf)


*********************************************

This throws the following error in Query Analyser:

Server: Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'IF'.
Server: Msg 170, Level 15, State 1, Line 14
Line 14: Incorrect syntax near 'tblInfoData'.

Any help/ideas?

thanx
</c>

View 6 Replies View Related

SQL Query Error - Datetime Timestamp??

Oct 3, 2007

So, what's wrong with this datetime syntax to the sql query? I'm getting error here..


Code:


insert into tbl_database_profile
(latest_date, version)
values
(datetime, '1')



The datatype for the field "latest_date" is datetime....

Thanks...

View 3 Replies View Related

Unknown Datetime Error In Query (Overflow At Runtime)

Aug 2, 2005

Hi all,

I have the following query...

SELECT    Count(*)
FROM        Incidents I
WHERE (Priority = 1)
AND (Time_First_Unit_On_Scene IS NOT NULL)
AND (DateDiff(s, Time_ClockStart, Time_First_Unit_On_Scene) <= 480) 
AND (Response_Date BETWEEN '1-Apr-2004')
AND ('31-Mar-2005 23:59:59')
AND (I.Disposition_ID <> 9 )

...and I get the following error message...

 System.Data.OleDb.OleDbException: Difference of two datetime columns caused overflow at runtime.

... any one know what it could be?

Thanks

Tryst

View 1 Replies View Related

SQL Query: Finding Records Between Datetime Inside Datetime

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

SQl Query Related Problem

Nov 26, 2004

Locally I have a table named 'countrydetails' in sql server which is created by dbo. I create asp.net page trying to show the details of the country. I write a query in vb.net page and build the project and try to run it, its giving me results.

The same vb.net project is uploaded and put online and when I try to see the details of the country online inspite of having records i m getting no results. In this case the table which was created is having username other than dbo.

I want my query written in vb.net page to run irrespective of the different users who have the database table. The page should run for both dbo and other users also. How do I achieve this? Please advise.

Thanks.


Regards,


Sumis

View 1 Replies View Related

Related Rooms Query

Jul 20, 2005

hi, im working on this for a long time. i'm using MSsql-server2000i have a table [visits] that records users visits to rooms. the columns areroom_id, user_id, visits.i want to write a query that can calculate the top 10 rooms that are relatedto any given room. i was thinking of firstly making a function that countshow many users visited both room A and room B, and then running thisfunction on A and all other rooms, and order by the result. i keep gettingweird errors when doing that. please elaborate.

View 2 Replies View Related

SQL Server Job Related Query

Jan 17, 2008

Hello all,
I have a job in sqlserver 2005 that has 2 steps. step 1 actualy verifies the netbackup files and step2 deletes those files from the disk if it is already being backed in netbackup. But this job is failing and in the job history i am seeing the errors
"The job was stopped prior to completion by User "username". The Job was invoked by Schedule 2 (Schedule1). The last step to run was step 1 (NetbackupStatus)." This is master slave job and this job is working fine in all the other servers except this one. The sql server service and the agent are running on the acct. which is an admin on that box. Can someone help me out please.

View 5 Replies View Related

SQL Error (SQL Express Related)

Apr 26, 2006

Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.
This is the error i get every now and then when i attempt to browse to a page. It seems that i am getting this error as its trying to connection to a sql express database??? Only i have set my project up to use sql server 2000 and i am using that DB but every now and then i get this error. Im not sure what else i need to do? Any ideas?

View 4 Replies View Related

CXPACKET Error Related To MOM Process

May 23, 2007

While MOM processes are running at some point, a process goes into deadlock and uses up all existing CPUs.

Sysprocesses shows this it opened up 4 threads and program name is Microsoft® Reliability Analysis Service.



Profiler doesn't show which command it was trying to execute, but last notable command which has started was MRAS_pcLoad EXECUTE @i_Return_Code = sp_getapplock @Resource = N'MOM.Datawarehousing.DTSPackageGenerator.exe', @LockMode = N'Exclusive', @LockOwner = N'Session', @LockTimeout =



Can you please help us, what could be the problem. It has been running fine till couple of days back.



--Prabhu

View 5 Replies View Related

Issue Related To Execution Time Of Sql Query

Apr 30, 2008

I have created 2 tables in a database which are mostly similar, the table1 will execute with more speed (take only less than or equal to 1 sec) but the table2 will take 4 or 5 secs to execute the query,moreover the similar datas was presented in both the tables. the eg:- query that i have executed is select max(c_code) from table1 and select max(c_code) from table2, the first one take less than 1 sec and the second one take more than 4 or 5 secs, also there is a procedure i hve written to update both the tables, and i got the time out error, if the table1 alone is updated using the procedure it is OK but the table2 alone is updated using the procedure the time out error will be shown, pls reply the reason for this problem as early as possible, it will be a grateful if anybody reply for this trouble?

View 2 Replies View Related

Related Tables: Help Needed With JOIN Query

Oct 16, 2006

Hi Group,My apologies for the lengthy post, but here goes...I have the following tables:TABLE Vehicles([ID] nvarchar(5),[Make] nvarchar(20),[Model] nvarchar(20),)TABLE [Vehicle Status]([ID] int, /* this is an auto-incrementing field*/[Vehicle ID] nvarchar(5), /* foriegn key, references Vehicles.[ID] */[Status] nvarchar(20),[Status Date] datetime)Here's my problem...I have the following data in my [Vehicles] and [Vehicle Status] tables:[ID] [Make] [Model]----------------------H80 Nissan SkylineH86 Toyota Aristo[ID] [Vehicle ID] [Status] [Status Date]----------------------------------------1 H80 OK 2006-10-012 H80 Damage 2006-10-053 H86 OK 2006-10-134 H86 Dent 2006-10-155 H86 Scratched 2006-10-16I need a query that will join the two tables so that the most recentstatus of each vehicle can be determined. I've gotten as far as:SELECT Vehicle.[ID], Make, Model, [Status], [Status Date] FROM[Vehicles] INNER JOIN [Vehicle Status] ON [Vehicles].[ID] = [VehicleStatus].[Vehicle ID]Of course this produces the following results:[ID] [Make] [Model] [Status] [Status Date]--------------------------------------------H80 Nissan Skyline OK 2006-10-01H80 Nissan Skyline Damage 2006-10-05H86 Toyota Aristo OK 2006-10-13H86 Toyota Aristo Dent 2006-10-15H86 Toyota Aristo Scratched 2006-10-16How do I filter these results so that I get only the MOST RECENT vehiclestatus?i.e:[ID] [Make] [Model] [Status] [Status Date]--------------------------------------------H80 Nissan Skyline Damage 2006-10-05H86 Toyota Aristo Scratched 2006-10-16Thanks in advance,Rommel the iCeMAn*** Sent via Developersdex http://www.developersdex.com ***

View 1 Replies View Related

Urgent: Help Regarding Query To Update Dates In Related Tables

May 28, 2007

I am really stuck here,
I hope to get some helpful answers on this forum.

Ok, I have three four tables in my db,
1- Stages
2- Activities
3- Tasks
4- Subtasks
Structure is like:
Stages
1- SrNo (Unique)
2- Stage
3- StartDate
4- FinishDate


Activities
1- SrNo (Unique)
2- Activity
3- StartDate
4- FinishDate
5- SrNo1 ((FK) from Stages Table)

Tasks
1- SrNo (Unique)
2- Task
3- StartDate
4- FinishDate
5- SrNo1 ((FK) from Activities Table)

Subtasks
1- SrNo (Unique)
2- Subtasks
3- StartDate
4- FinishDate
5- SrNo1 ((FK) from Task Table)

Now what i want is to update Tasks, StartDate and FinishDate according the Min(StartDate) and Max(FinishDate) of related Subtasks and same for Activities and Stages.
I have tried following query to Update tasks, StartDate and FinishDate

Update Tasks Set Tasks.StartDate=(Select Min(Subtasks.StartDate) from Subtasks,Tasks where Tasks.SrNo=Subtasks.SrNo1) from Subtasks,Tasks where Tasks.SrNo=Subtasks.SrNo1

But this query updates all Tasks with Min and Max date from Subtasks regardless of their relation.

Any help, would be appreciated.
Thanks

View 3 Replies View Related

Strage Error Related To Stored Procedure Parameter

Sep 19, 2005

Can anyone explain what's wrong with this code which is supposed to populate a dropdownlist using a parametised stored procedure and SqlDataAdapter?
objCmd = new SqlCommand("bs_GetActivityValueTypes", objConn.GetConnection()); objCmd.Parameters.Add("@scid", SqlDbType.Int);objCmd.Parameters["@scid"].Value = Convert.ToInt32(Request.QueryString["scorecard"].ToString()); objAdapter = new SqlDataAdapter(objCmd);objAdapter.Fill(dsInitData, "tblValueTypes");
comboResultType.DataSource = dsInitData.Tables["tblValueTypes"]; comboResultType.DataValueField = "Type_ID";comboResultType.DataTextField = "Type_Desc";comboResultType.DataBind(); comboResultType.Items.Insert(0, new ListItem("- Select a value type -", ""));Basically, if I remove the parameter from the code and the stored procedure it works fine, but when I add the parameter back I get an "Incorrect syntax near 'bs_GetActivityValueTypes'" error at line: objAdapter.Fill(dsInitData, "tblValueTypes"); This makes no sense to me because I know the stored procedure is well formed and I've used almost identical code elsewhere. All variables are declared further up the code. Someone help please!

View 3 Replies View Related

Nested SELECT Query That Also Returns COUNT From Related Table

Mar 4, 2005

OK heres the situation, I have a Categories table and a Products table, each Category can have one or many Products, but a product can only belong to one Category hence one-to-many relationship.

Now I want to do a SELECT query that outputs all of the Categories onto an ASP page, but also displays how many Products are in each category eg.

CatID | Name | Description | No. Products

0001 | Cars | Blah blah blah | 5

etc etc

At the moment I'm doing nesting in my application logic so that for each category that is displayed, another query is run that returns the number of products for that particular category. It works ok!

However, is there a way to write a SQL Statement that returns all the Categories AND number products from just the one SELECT statement, rather than with the method I'm using outlined above? The reason I'm asking is that I want to be able to order by the number of products for each category and my method doesn't allow me to do this.

Many thanks!

View 3 Replies View Related

Transact SQL :: Get DataTypes And Other Schema Related Data Of Columns In A Query

Sep 11, 2015

Is it possible to get data-type,default-value, etc. (basically schema information) of columns in a particular query.This is something similar to what we get when we execute the following 

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Employees'

But instead of TABLE_NAME i would like to give a query like

SELECT [EmployeeID],[LastName],[FirstName] from [dbo].[Employees] order by [EmployeeID].

View 10 Replies View Related

Profiler Does Not Comment Out Security Related Commands When There Is An Error In The Batch

Aug 1, 2006

i am testing some encryption
scenarios ,in profiler the statements like "OPEN KEY" and all "Encrypt"
and "Decrypt" functions are removed automaticly from the trace
and replaced with a comment ,create a trace and try the code i attached ,
you will see in profiler trace that that all encryption related commands
are commented out ,this is what expected.

but now go to the batch and comment out the "SELECT @rrr' statment,
and run the batch ,this batch will fail beacuse "@rrr" is not declared,
now go back to profiler and you will see that for the failed batch
all the encryption command are NOT COMMENTED OUT !!!
esspecially important is the visibility of the password of the open key command.


seems like a very dangerous bug to me!!!

CREATE CERTIFICATE test1
ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
WITH SUBJECT = 'Sammamish Shipping Records',
EXPIRY_DATE = '10/31/2009';
GO

CREATE SYMMETRIC KEY Key09 WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE test1;
GO
declare @Str nvarchar(100)
declare @Enc varbinary(max)
set @Str = 'encrypt this'
OPEN SYMMETRIC KEY Key09
decryption by CERTIFICATE test1 WITH PASSWORD = 'pGFD4bb925DGvbd2439587y'

SET @Enc
= EncryptByKey(Key_GUID('Key09'), @Str);
---select @rrr
select CONVERT(nvarchar(100), DecryptByKey(@Enc))
go




View 1 Replies View Related

Analysis :: DAX For Distinct Count Of Related Column Without Returning Out Of Memory Error?

Aug 10, 2015

I am doing a distinct count on a related table's column, but get an out of memory error if I run it for the entire table (works great for just a few rows when filtered down).The error I get is: "We couldn't get data from the external source.The operation has been cancelled because there is not enough memory available for the application.  If using 32-bit version of the product consider upgrading.

I know I can add a related column and that works fine...but that seems to me like I've defeated the purpose, I have a good and proper lookup table, and should be able to run my query against its relationship.Here is the query and details below *Note I supplied a scaled down sample, on my actual model I receive these errors, not in the sample as it has only a few rows

List Workers Distinct Project Customers:=CALCULATE(DISTINCTCOUNT(Projects[CustomerID]),'WorkersToProjects')
Other measure which returns no errors, but included for completeness:
List Workers Projects:=CALCULATE(DISTINCTCOUNT([ProjectID]),ISBLANK(WorkersToProjects[ProjectID])=FALSE())

My goal here is to allow the user to view the workers assigned to a project, but also get counts of the workers assigned to the CUSTOMER of a project. For example, suppose we lose a customer, we want to see how many workers would be impacted by that, so a count of projects per worker is not useful there, we need to see a count of workers per project's customer (owner of project whom project work is being done for)The question being: How can I accomplish this:

1. WITHOUT adding a calculated column to WorkersToProjects (of Projects.CustomerID)
2. WITH better performance?

There must be a better way to write this DAX to still get the correct answer?*Pic of pivot table, again, the numbers are accurate but the formula used to List Workers Distinct Project Customers measure does NOT scale :( 3 count for red , the number of Projects John has and 2 count for blue, the unique customers/owners of those projects "Veridian Dynamics" and "Massive Dynamic". URL....

View 3 Replies View Related

Query Not Returning Proper Data (date Related) In 2005 After Upgrade From 2000...

Jun 7, 2007

I am sending out an SOS.

Here is the situation:

We recently upgrade to 2005(sp). We have one report that ran fine in 2000 but leaves out data from certain columns (date related) in the results, so we chalked it up to being a non compatiable issue. So, I decided to try and switch the DB back to 2000 compatibility (in our test env) and then back to 2005. After that the report started returning the proper data. We can€™t really explain why it worked but it did. So we thought we would try it in prod (we knew it was a long shot) and it didn€™t work. So the business needs this report so we thought we would refresh the test system from prod, but now we are back to square one. I was wondering if anyone else has heard or seen anything like this. I am open to any idea€™s, no matter how crazy. J The systems are configured identically. Let me know if you need more information.

Thank you. Scott

View 4 Replies View Related

ERROR:Syntax Error Converting Datetime From Character String. With Stored Procedure

Jul 12, 2007

Hi All,





i have migrated a DTS package wherein it consists of SQL task.

this has been migrated succesfully. but when i execute the package, i am getting the error with Excute SQL task which consists of Store Procedure excution.



But the SP can executed in the client server. can any body help in this regard.





Thanks in advance,

Anand

View 4 Replies View Related

Error 241: Syntax Error Converting Datetime From Character String

Jan 7, 2004

Hi All, can someone help me,
i've created a stored procedure to make a report by calling it from a website.
I get the message error "241: Syntax error converting datetime from character string" all the time, i tryed some converting things but nothig works, probably it is me that isn't working but i hope someone can help me.
The code i use is:


CREATE proc CP_Cashbox @mID varchar,@startdate datetime,@enddate datetime
as
set dateformat dmy
go
declare @startdate as varchar
declare @enddate as varchar

--print "query aan het uitvoeren"

select sum(moneyout) / sum(moneyin)*100 as cashbox
from dbo.total
where machineID = '@mID' and njdate between '@startdate' and '@enddate'
GO



Thanx in front
Cya

View 14 Replies View Related

Getting Error : : The Conversion Of A Char Data Type To A Datetime Data Type Resulted In An Out-of-range Datetime Value

Jan 28, 2008

update tblPact_2008_0307 set student_dob = '30/01/1996' where student_rcnumber = 1830when entering update date in format such as ddmmyyyyi know the sql query date format entered should be in mmddyyyy formatis there any way to change the date format entered to ddmmyyyy in sql query?

View 5 Replies View Related

Datetime Query?

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

Datetime In In Sql Query

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

Need Help With DateTime Query

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) &gt;= 15 AND YEAR(arrivalDate) = 2008) AND (MONTH(departureDate) ?????

View 5 Replies View Related

Datetime Query

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

Datetime Query

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

Datetime Query

Sep 18, 2007

How to display records between datetime field??

View 7 Replies View Related

Need Help With DateTime Query

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

Get Max Value By Datetime - In Query

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

Help With A DATETIME Query

Jul 20, 2005

Hi,I have a table called Bookings which has two important columns;Booking_Start_Date and Booking_End_Date. These columns are both of typeDATETIME. The following query calculates how many hours are availablebetween the hours of 09.00 and 17.30 so a user can see at a glance how manyhours they have unbooked on a particular day (i.e. 8.5 hours less the timeof any bookings on that day). However, when a booking spans more than oneday the query doesn't work, for example if a user has a booking that startson day one at 09.00 and ends at 14.30 on the next day, the query returns 3.5hours for both days. Any help here would be greatly appreciated.SELECT 8.5 - (SUM(((DATE_FORMAT(B.Booking_End_Date, '%k') * 60 ) +DATE_FORMAT(B.Booking_End_Date, '%i')) - ((DATE_FORMAT(B.Booking_Start_Date,'%k') * 60 ) + DATE_FORMAT(B.Booking_Start_Date, '%i'))) / 60) ASAvailable_Hours FROM WMS_Bookings B WHERE B.User_ID = '16' ANDB.Booking_Status <> '1' AND NOT ( '2003-10-07' <DATE_FORMAT(Booking_Start_Date, "%Y-%m-%d") OR '2003-10-07' >DATE_FORMAT(Booking_End_Date, "%Y-%m-%d") )Thanks for your help

View 1 Replies View Related

[Query] - Query Designer Encountered A Query Error:Unspecified Error

Jan 22, 2001

Hi,

I get this error dialog when I try to open all the rows of any table from Enterprise manager..

Any help would be really appreciated..

Thanks,
-Srini.

View 1 Replies View Related

SQL Query Question (Datetime)

Nov 7, 2005

Hello ,I have a table field named BookedDateTime and its date type is DateTime. It contains the order booked date and time. I have a query to list all the orders which happens on specific date, no matter what time during the day.Because the BookedDateTime combines the date and time, I cannot get rid of the time. So each time when I do the query , I get nothing. How can I solve the problem?Thanks,

View 1 Replies View Related







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