Order By Datediff

Jan 18, 2014

I'm looking to apply a secondary order to a select query based on the difference in hours between now and a stored date/time. Effectively I'd like to place records with a datediff of over 2 hours to the top of the sort;

ORDER BY DATEDIFF(hour,FullLatestCallTimeStamp,Getdate() > 2) DESC

My Problem is that I get a syntax error on the '>'

View 2 Replies


ADVERTISEMENT

Express Will Not Load. Insurmountable Difficulties With Order Of Uninstalls/order Of Installs/ Suggestions Plz

Jan 7, 2007

Finding the "pieces of information" I need to successfully install the SQL Server Express edition is so complex. Uninstalls do "not" really uninstall completely, leading to failure of SQL install. Can you suggest a thorough, one-stop site for directions for the order of app uninstalls and then the order for app installs for the following...

SQL Server Express edition

Visual Studios 2005

Jet 4.0 newest upgrade

.Net Framework 2.0 (or should I use 3.0)

VS2005 Security upgrade

Anything else I need for just creating a database for my VS2005 Visual Basic project?

I was trying to use MS Access as my backend db but would like to try SQL Express



Thank you, Mark





View 7 Replies View Related

Conditional Order By - Sort Result Set By Employee Number Ascending Order

Sep 24, 2012

In SQL sERVER 2008, I have two fields - Depatment and Employees. I need to sort the result set by employee number ascending order, with following exception

1)when department number = 50 - the preferred order is Employee # - 573 followed by 551-572 (employee # belong to Dept 50 = 551-573)

2)When Department number = 20 – the preferred sort order is Employee # 213-220, followed by Employee # 201-213 (employee # belong to Dept 20 = 201-220)

How shall I achieve this?

View 4 Replies View Related

Analysis :: Order Of Rows In Tabular Table Not In Same Order Data Was Retrieved?

May 19, 2015

I never paid much attention to this before but I noticed this today in a new table I was creating.

For tables defined in the tabular model the table properties have something like SELECT Blah FROM TableName ORDER BY Blah Then in the tabular model the table's data is in the same order it was ordered by in the data source for the table.

I have a date table I setup and I noticed it is NOT respecting the sort order.

I have it sorted by DateID which sorts with the oldest date first and newest date as last row.However, the table that is imported and stored in the data model is not in that order.

I can of course manually sort the rows in BIDS/DataTools, but I find this discrepancy odd.

Would this have negative impacts on the EARLIER function for example if the data rows are not in the order specified?

View 8 Replies View Related

Calculate Total Amount Of Order Details Based On Particular Order

Apr 10, 2014

I have a query that calculate the total amount of order details based on a particular order:

Select a.OrderID,SUM(UnitPrice*Quantity-Discount)
From [Order Details]
Inner Join Orders a
On a.OrderID=[Order Details].OrderID
Group by a.OrderID

My question is what if I wanted to create a formula to something like:

UnitPrice * Quantity - DiscountAmount Where DiscountAmount = UnitPrice Quantity * Discount

Do I need to create a function for that? Also is it possible to have m y query as a table variable?

View 7 Replies View Related

Default Sort Order - Open Table - Select Without Order By

Mar 27, 2008

Hi!

I recently run into a senario when a procedure quiered a table without a order by clause. Luckily it retrived data in the prefered order.

The table returns the data in the same order in SQL Manager "Open Table"

So I started to wonder what deterimins the sort order when there is no order by clause ?

I researched this for a bit but found no straight answers. My table has no PK, but an identiy column.

Peace.

/P

View 5 Replies View Related

How To Add Order Item Into A Purchase Order Using A Stored Procedure/Trigger?

Jan 4, 2008

Hey guys, i need to find out how can i add order items under a Purchase Order number.
My table relationship is PurchaseOrder ->PurchaseOrderItem.

below is a Stored Procedure that i have wrote in creating a PO:



CREATE PROC spCreatePO (@SupplierID SmallInt, @date datetime, @POno SmallInt OUTPUT)

AS

BEGIN

INSERT INTO PurchaseOrder (PurchaseOrderDate, SupplierID) VALUES(@date, @SupplierID)

END



SET @POno = @@IDENTITY

RETURN


However, how do i make it that it will automatically adds item under the POno being gernerated? can i use a trigger so that whenever a Insert for PO is success, it automaticallys proceed to adding the items into the table PurcahseOrderItem?


CREATE TRIGGER trgInsertPOItem

ON PurchaseOrderItem

FOR INSERT

AS

BEGIN


'What do i entered???'
END

RETURN


help is needed asap! thanks!

View 14 Replies View Related

Find Order By Date Range Or Order Id

May 8, 2007

hi basically what i have is 3 text boxes. one for start date, one for end date and one for order id, i also have this bit of SQL
SelectCommand="SELECT [Order_ID], [Customer_Id], [Date_ordered], [status] FROM [tbl_order]WHERE (([Date_ordered] >= @Date_ordered OR @Date_ordered IS NULL) AND ([Date_ordered] <= @Date_ordered2 OR @Date_ordered2 IS NULL OR (Order_ID=ISNULL(@OrderID_ID,Order_ID) OR @Order_ID IS NULL))">
 but the problem is it does not seem to work! i am not an SQL guru but i cant figure it out, someone help me please!
Thanks
Jez

View 4 Replies View Related

Default Sort Order When Order By Column Value Are All The Same

Apr 14, 2008

Hi,
We got a problem.
supposing we have a table like this:

CREATE TABLE a (
aId int IDENTITY(1,1) NOT NULL,
aName string2 NOT NULL
)
go
ALTER TABLE a ADD
CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId)
go


insert into a values ('bank of abcde');
insert into a values ('bank of abcde');
...
... (20 times)

select top 5 * from a order by aName
Result is:
6Bank of abcde
5Bank of abcde
4Bank of abcde
3Bank of abcde
2Bank of abcde

select top 10 * from a order by aName
Result is:
11Bank of abcde
10Bank of abcde
9Bank of abcde
8Bank of abcde
7Bank of abcde
6Bank of abcde
5Bank of abcde
4Bank of abcde
3Bank of abcde
2Bank of abcde

According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. :eek:

Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot.

So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?

View 14 Replies View Related

Recordset's Order And Database's Physical Order?

Jul 20, 2005

Hi,guys!I have a table below:CREATE TABLE rsccategory(categoryid NUMERIC(2) IDENTITY(1,1),categoryname VARCHAR(20) NOT NULL,PRIMARY KEY(categoryid))Then I do:INSERT rsccategory(categoryname) VALUES('url')INSERT rsccategory(categoryname) VALUES('document')INSERT rsccategory(categoryname) VALUES('book')INSERT rsccategory(categoryname) VALUES('software')INSERT rsccategory(categoryname) VALUES('casus')INSERT rsccategory(categoryname) VALUES('project')INSERT rsccategory(categoryname) VALUES('disert')Then SELECT * FROM rsccategory in ,I can get a recordeset with the'categoryid' in order(1,2,3,4,5,6,7)But If I change the table definition this way:categoryname VARCHAR(20) NOT NULL UNIQUE,The select result is in this order (3,5,7,2,6,4,1),and 'categoryname 'in alphabetic.Q:why the recordset's order is not the same as the first time since'categoryid' is clustered indexed.If I change the table definition again:categoryname VARCHAR(20) NOT NULL UNIQUE CLUSTEREDthe result is the same as the first time.Q:'categoryname' is clustered indexed this time,why isn't in alphabeticorder?I am a newbie in ms-sqlserver,or actually in database,and I do havesought for the answer for some time,but more confused,Thanks for yourkind help in advance!

View 2 Replies View Related

Default Sort Order When The Order By Column Value Are All The Same

Apr 14, 2008

Hi,
We got a problem.
supposing we have a table like this:

CREATE TABLE a (
aId int IDENTITY(1,1) NOT NULL,
aName string2 NOT NULL
)
go
ALTER TABLE a ADD
CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId)
go

insert into a values ('bank of abcde');
insert into a values ('bank of abcde');
...
... (20 times)

select top 5 * from a order by aName
Result is:
6 Bank of abcde
5 Bank of abcde
4 Bank of abcde
3 Bank of abcde
2 Bank of abcde

select top 10 * from a order by aName
Result is:
11 Bank of abcde
10 Bank of abcde
9 Bank of abcde
8 Bank of abcde
7 Bank of abcde
6 Bank of abcde
5 Bank of abcde
4 Bank of abcde
3 Bank of abcde
2 Bank of abcde

According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users.
Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot.
So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?

View 5 Replies View Related

Order By Clause In View Doesn't Order.

May 18, 2006

I have created view by jaoining two table and have order by clause.

The sql generated is as follows

SELECT TOP (100) PERCENT dbo.UWYearDetail.*, dbo.UWYearGroup.*
FROM dbo.UWYearDetail INNER JOIN
dbo.UWYearGroup ON dbo.UWYearDetail.UWYearGroupId = dbo.UWYearGroup.UWYearGroupId
ORDER BY dbo.UWYearDetail.PlanVersionId, dbo.UWYearGroup.UWFinancialPlanSegmentId, dbo.UWYearGroup.UWYear, dbo.UWYearGroup.MandDFlag,
dbo.UWYearGroup.EarningsMethod, dbo.UWYearGroup.EffectiveMonth



If I run sql the results are displayed in proper order but the view only order by first item in order by clause.

Has somebody experience same thing? How to fix this issue?

Thanks,

View 16 Replies View Related

Inconsistent Sort Order Using ORDER BY Clause

Mar 19, 2007

I am getting the resultset sorted differently if I use a column number in the ORDER BY clause instead of a column name.

Product: Microsoft SQL Server Express Edition
Version: 9.00.1399.06
Server Collation: SQL_Latin1_General_CP1_CI_AS

for example,

create table test_sort
( description varchar(75) );

insert into test_sort values('Non-A');
insert into test_sort values('Non-O');
insert into test_sort values('Noni');
insert into test_sort values('Nons');

then execute the following selects:
select
*
from
test_sort
order by
cast( 1 as nvarchar(75));

select
*
from
test_sort
order by
cast( description as nvarchar(75));

Resultset1
----------
Non-A
Non-O
Noni
Nons

Resultset2
----------
Non-A
Noni
Non-O
Nons


Any ideas?

View 4 Replies View Related

Datediff

Dec 5, 2005

If I wanted to get everyone DOB who is over 18 how would I do that? I am currently trying something like this, but no luck...


Declare @todays_date datetime

Select from person
CASE
WHEN dateadd(year, datediff (year, Date_Of_Birth, @Todays_Date), Date_Of_Birth) > @Todays_Date -- Date of Birth check
THEN datediff (year, Date_Of_Birth, @Todays_Date) - 1
ELSE datediff (year, Date_Of_Birth, @Todays_Date)
END
>= 18

View 3 Replies View Related

DateDiff

Jul 27, 2004

I'm using a datediff(mi, start, stop) to get the duration of an operation. i want it displayed in HH:MM format. can anyone help me w/ a way to do that????



tia,
e3witt

View 4 Replies View Related

Datediff Or MAX?

Mar 9, 2004

Hello,

My string is:

SELECT weight FROM progress WHERE dateInput = (SELECT MAX(dateInput) FROM progress) AND memberID = 1

The problem is that the MAX dateInput doesn't belongs to memberID 1. It belongs to memberID 2. What I want is that I wanna choose the MAX Date of memberID 1. I thought of maybe using datadiff function. But I don't know how to make the datediff statement. Maybe I can write the datediff statement whereby the least datediff between the dateInput and getdate() will be the row I want. I really appreciate the person that helps me this problem... Thanks 1st of all!

View 2 Replies View Related

DateDiff

Apr 15, 2004

I am trying to select records from whatever the current date would be and 12 months before whatever the current date is. How would I go about doing this. The table that I am trying to do this with has a year column and a month column.

I was playing with the date diff function, but I can only get dates from the specified date range. I need it to be where if I run it tomorrow, it will get that day and everything within the last 12 months.

View 7 Replies View Related

DATEDIFF?

Apr 25, 2008

I need to get a listing of all persons who are atleast 18 years of age. A date of birth field in the database is in this format:

4/25/2008 12:00:00 AM

My solution would be where the difference between the current date and the dob is >= 18.

I tried...

select * from table where datediff(yy,dob,getdate)) >= 18.

But this only seems to subtract the years and ignore the days/months, which I need. Could anyone provide the syntax I need?

Help is appreciated. Thanks.

View 3 Replies View Related

Datediff

Jun 24, 2008

Hi there i am using the datediff funtion but it does not seem to be inclusive of the two dates eg
SELECT DATEDIFF(day, s_Date, e_date) AS NumberOfDays,*
FROM weekendtest

i know i could do
SELECT DATEDIFF(day, s_Date, e_date)+1 AS NumberOfDays,*
FROM weekendtest

but i was just wondering if there some other function i should use that would be more appropriate

View 3 Replies View Related

Datediff

Oct 21, 2004

Hi
I've a problem!!!
i want to use datediff thats no problem. But the first parameter has to
be parameterised. What datatype do i declare it as. i declare it as varchar
it returns me an error : Invalid parameter 1 specified for datediff.

i'm doing this in a stored procedure.

Is there a way out or do i've to use the good old 'IF' or 'Case' ?



regards
pradeep

View 1 Replies View Related

DateDiff In Sql?

Oct 19, 2006

Hi,
I have a Table Where I have Two Date columns and I want to find the Differnce in the 3rd column.ie DateReceived - AsOnDate = NoofDays.I tried this code
SELECT SRNO,CONVERT (VARCHAR,AsonDate,102)as ASONDATE,CONVERT(VARCHAR, DateReceived, 102) AS DateReceived,CONVERT(VARCHAR, DateAcknowledged, 102) AS NoofDays,
sum(CASE WHEN DATEDIFF(DAY, DATERECEIVED,ASONDATE, GETDATE())
FROM Details


Plz help me...

Thanks

View 7 Replies View Related

DATEDIFF

Jan 27, 2008

SELECT DATEDIFF(mm,0,'01/25/2008')
Returns 1296 what does it represent...

Thanks

View 2 Replies View Related

DATEDIFF

Feb 29, 2008

Could somebody please explain to me why I get the following results:


select datepart (ww, '10/02/08') --returns 6

select datepart (ww, '18/02/08') --returns 8

select datediff (ww, '10/02/08', '18/02/08') -- returns 1

I was expecting the third query to return 2.

Thanks.

View 11 Replies View Related

HELP Me In This Datediff() Function....

Nov 23, 2006

Hi, I am facing problem rite now.. I want to calculate the date different minutes between 23:00:00 and 01:00:00.
My code :
datediff(Minute,'01:00:00','23:00:00')
The result is 1320 minutes. (22 hours)... But, the result that I want is 120 minutes (2 hours)....
Can anybody help ???
Thanks in advance...

View 2 Replies View Related

Datediff Problem

Jan 12, 2007

I'm trying to use the same select statement I used in classic SQL, but it isn't working.  I get a "The server tag is not well formed." error.
I'm trying to return all the records where the date in a particular field are over 60 days old.  My select statement is:
"SELECT [ReNewAd], [Male or Female], [File2], [PHOTO], [First Name], [Last Name], [City], [State], [NewAd] FROM Members Where ('NewAd' IS NOT NULL AND (DateDiff('d', [NewAd], '" & Now() & "') <= 60)) order by [NewAd] Desc"
I've been looking through the datediff posts, but I'm not making any sense of what I'm reading.  Can anyone see what I'm doing wrong?
Thanks, Diane

View 5 Replies View Related

Using CASE And DATEDIFF

Jun 14, 2007

I'm trying to create a stored procedure that will return a set of data. An input parameter (if specified) will determine what topics will be show based on the date the item "datetime". ALTER PROCEDURE [dbo].[discussions_GetTopics]@board_id as int,@PageNumber INT,@TopicsDays  INT = NULL,@TopicsPerPage INT,@HowManyProducts INT OUTPUTAS-- Declare the table that will store all the topics for the given board_idDECLARE @Topics TABLE(RowNumber INT,topic_id INT,topic_title VARCHAR(50),topic_replies INT,topic_views INT,topic_type INT,post_id int,post_time DATETIME,Topic_Author_UserName nvarchar(256),Topic_Author_ID uniqueidentifier,Post_Author_Username nvarchar(256),Post_Author_ID uniqueidentifier)-- populate the tableINSERT INTO @TopicsSELECT     ROW_NUMBER() OVER (ORDER BY discussions_topics.topic_id), discussions_Topics.topic_id, discussions_Topics.topic_title, discussions_Topics.topic_replies, discussions_Topics.topic_views, discussions_Topics.topic_type, discussions_Posts.post_id, discussions_Posts.post_time, user_1.UserName AS Topic_Author_Username,                      user_1.UserId AS Topic_Author_ID, user_2.UserName AS Post_Author_Username, user_2.UserId AS Post_Author_IDFROM         discussions_Topics INNER JOIN                      discussions_Posts ON discussions_Posts.post_id = discussions_Topics.topic_last_post_id INNER JOIN                      aspnet_Users AS user_1 ON user_1.UserId = discussions_Topics.topic_poster INNER JOIN                      aspnet_Users AS user_2 ON user_2.UserId = discussions_Posts.poster_idWHERE     (discussions_Topics.board_id = @board_id ANDCASE @TopicsDays    WHEN '1' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 1    WHEN '2' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 7    WHEN '3' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 14    WHEN '4' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 30    WHEN '5' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 90    WHEN '6' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 180    WHEN '7' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 365END)-- return the total number of products using an OUTPUT variableSELECT @HowManyProducts = COUNT(topic_id) FROM @Topics-- Return the specified page of topicsSELECT * from @TopicsWHERE RowNumber > (@PageNumber - 1) * @TopicsPerPageAND RowNumber <= @PageNumber * @TopicsPerPage  I need help where the  "CASE @TopicsDays
    WHEN '1' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 1
    WHEN '2' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 7
    WHEN '3' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 14
    WHEN '4' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 30
    WHEN '5' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 90
    WHEN '6' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 180
    WHEN '7' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 365
END" Is...   Im not doing it right. Can someone help me?

View 1 Replies View Related

DateDiff / DateAdd - Please Help Me:(

Sep 28, 2007

hello,
i have a Pictures table:  PictureID, Name, Description, DateAdded (GETDATE() when insert), IsActive...
i need to make some stored procedures to show me the pictures added in last 24hours, in last 3 days, last 2 weeks and so on
the pictures added in database are active (available to be seen by users) only 1yaer after the date added
I tryied to make a stored procedure (in fact i maked a lots of them, for 1day 3 days 1 week 1 month), but i have a problem with that DateDiff and DateAdd
Here is what i tryied CREATE PROCEDURE LastAdded_2monthsAgo

AS

SELECT Pictures.ProductID, Pictures.Name, Pictures.Description, Pictures.DateAdded
FROM Pictures
WHERE (DATEDIFF(month, Pictures.DateAdded, GETDATE()) >= 0) AND (DATEDIFF(month, Pictures.DateAdded, GETDATE()) <= 2)
ORDER BY DateAdded DescI have a feeling that is wrong, please make your own version and show me what i should write...I don't know what should be first the today date or the DateAdded...i need to select the last added products from a specific interval of time...Should i do something with that "1 year available" like  WHERE (DATEDIFF(month, GETDATE(), DATEADD(year, 1, Products.DateAdded)) >= 0) AND (DATEDIFF(month, GETDATE(), DATEADD(year, 1, Products.DateAdded)) <= 2) I am sure is a stupid thig up there...if you can, make your own version how you would do it and show me..please help me

View 5 Replies View Related

Using DateDiff Function For Age

Nov 12, 2007

Hi,I'm using the datediff function to display the ages of the users in my database. However the age rounds up once they are 35.5 etc...I could create another function which works similar to the DateDiff function, but use math.floor to always round down, but I need to use this function in a SQL statement WHERE clause. Is there any way around this?Thanks,Curt. 

View 3 Replies View Related

Sql Server DATEDIFF

Apr 25, 2008

This is a sql question, so I'm hoping someone can give me some direction.
I need to query a table and return all rows where the individual is >= 18 years of age. It should involve simply subtracting the date of birth from the current date.  I thought the answer would be so simple:
 select * from table where datediff(yy,dob,getdate()) >= 18
Unfortunately, the results are inconsistent.  The DATEDIFF function, it appears, will not show correct results down to the month/day level.  I tried posting this on a sql server forum, but the recommendations I received involved coding some gigantic function.  It seems that there ought to be a simple 'WHERE' clause which could solve this.
If someone knows of a silver bullet, I would surely appreciate your advice.
Thank you.

View 5 Replies View Related

DATEDIFF In A WHERE Clause

May 6, 2008

I'm trying to include a DATEDIFF compare in my Where clause, but it seems to ignore the comparison I'm trying to invoke, and just returns all  rows.  When I display the result of the DATEDIFF  command, it looks good, but I can't seem to have it incorporated in my Where clause.
The following is my code -
 select [Pkg ID], [Elm (s)], [Type Name (s)], [End Exec Date], [End Exec Time]
from pkgactions
WHERE (8 > DateDiff(year, [End Exec Date], GetDate() ))
order by [pkg id]
 
Thanks

View 4 Replies View Related

DATEDIFF Possibilities

Aug 10, 2004

Hi,

I have two fields in one of my database table. They are date fields, one for the start and the other for the end of the "problem solving". What I want to do is to show the concrete elapsed time between the two.

My first problem is that I'm not able to show hour AND minutes. I wrote this for instance:
SELECT DATEDIFF(hour, fld_date_debut, fld_date_fin) As elapsed_time
I tried HH:mm instead of hour, but it is not working.

My second problem is that I can have more than one start and end hour for the same "problem solving". In the database it's like:

start hour: 10:00 End hour: 11:00 Number of the problem: 1
start hour: 13:00 End hour: 16:00 Number of the problem: 1

So I would like to add these to my elapsed time. I want (11:00 - 10:00) + (16:00 - 13:00)...but how can i do this in my SQL query ?

Thanks.

View 1 Replies View Related

DateTime Using DateDiff

Dec 8, 2004

Hello everyone.

Im currently using the DateDiff function to filter my DateTime columns but am finding it somewhat troublesome. Currently I am having to write the same select statement 3 times if I want to filter by month, year or all (ignoring dates).

To find @PurchaseTotal for the year, I have to write the following:


SELECT
@PurchaseTotal = Sum(PurchaseTotal)
FROM
_Expenses
WHERE
DateDiff(yyyy, DateOf, @IntervalDate) = @Interval



To find @PurchaseTotal for a month, I have to write the following:


SELECT
@PurchaseTotal = Sum(PurchaseTotal)
FROM
_Expenses
WHERE
DateDiff(mm, DateOf, @IntervalDate) = @Interval



To find @PurchaseTotal for all the records, I have to write the following:


SELECT
@PurchaseTotal = Sum(PurchaseTotal)
FROM
_Expenses


I've tried the following code but I get an error.

DateDiff(@DateParameter, DateOf, @IntervalDate) = @Interval

Error says something like "incorrect parameter 1 for DateDiff."

It seems you have to write a different select statement for month, day and year. Also If you want a total from all the records you have to write yet another select statement.

Does anyone know of a DateTime function that allows parameters to specify for month, day and Year? Also does anyone know of a DateTime function that works like the COALESCE function to where you can send it a NULL value and give you all the records?

Thank you ahead for any direction you can give.


Alec

View 1 Replies View Related

DateDiff Always Return 0

Feb 14, 2006

I just discover the result for my query that had use the function dateDiff seems to be in-correct, no matter what's the date, the dateDiff always return a zero. Am I using it in-correctly?
select DateDiff(mm,11/1/2004, 12/31/2005)
SELECT component_id, component_description,SUM(CASE DateDiff(mm,date_complete,'12/31/2004')WHEN 2 THEN component_qty ELSE 0 END) AS mm1,SUM(CASE DateDiff(mm,date_complete,'12/31/2004')WHEN 1 THEN component_qty ELSE 0 END) AS mm2,SUM(CASE DateDiff(mm,date_complete,'12/31/2004')WHEN 0 THEN component_qty ELSE 0 END) AS mm3,sum(component_qty) as totalFROM view_jobcomponent WHERE date_complete between '10/1/2004' and '12/31/2004' GROUP BY component_id, component_descriptionorder by component_id, component_description

View 1 Replies View Related







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