Getdate() with no time associated

Is there a command that will let me set getdate() a in a smalldatetime field so that the there is no time associated with it?

For example, I have a table that I want to load the date a user does an action. If I use getdate() I'll get a value such as 5/25/2006 08:26:56.340, whereas I would just like a value 5/25/2006.

I can work it out by doing the following: select (datename(month,getdate())+'-'+datename(day,getdate())+'-'
+datename(year,getdate()))

However it seems to me that there should be a simpler way.well, i dunno if it's simpler, but this is a lot more efficient --

dateadd(d,datediff(d,0,getdate()),0)|||Towards the bottom of this article is an explanation on the why and how :)

EDIT - how about I post the article link eh?
http://www.sql-server-performance.com/fk_datetime.asp|||That does seem more efficient (I knew there had to be a better approach). And thanks for the link to the article.|||fabulous link, pootle, thanks

Get top values

My task table contains the id of the task, the jobid that the task is
associated with, and a numeric value representing the priority of the
task. There can be multiple tasks for a job. I need a view that
returns the task id, and job id of the task with the higest priority
for each job. I tried grouping but this does not appear to work.
Thanks for the help.
Sean M. SeversonWith SQL 2005, you can try something like that :
with Ordering (JOB_ID, TASK_ID, RN) as
(select JOB_ID,
TASK_ID,
ROW_NUMBER() OVER (PARTITION BY JOB_ID ORDER BY PRIORITY)
from MyTable
)
select JOB_ID, TASK_ID
from Ordering
where RN=1
With SQL 2000, you should work with # tables or table variables. (with ORDER
BY ...)
JN.
"NerdRunner" <sseverson@.2sts.biz> a écrit dans le message de news:
1169075237.717157.102200@.s34g2000cwa.googlegroups.com...
> My task table contains the id of the task, the jobid that the task is
> associated with, and a numeric value representing the priority of the
> task. There can be multiple tasks for a job. I need a view that
> returns the task id, and job id of the task with the higest priority
> for each job. I tried grouping but this does not appear to work.
> Thanks for the help.
> Sean M. Severson
>|||I think this will do it.
SELECT *
FROM Tasks as A
WHERE TaskID = (SELECT TOP 1 TaskID
FROM Tasks as B
WHERE A.JobID = B.JobID
ORDER BY B.Priority DESC)
Roy Harvey
Beacon Falls, CT
On 17 Jan 2007 15:07:17 -0800, "NerdRunner" <sseverson@.2sts.biz>
wrote:
>My task table contains the id of the task, the jobid that the task is
>associated with, and a numeric value representing the priority of the
>task. There can be multiple tasks for a job. I need a view that
>returns the task id, and job id of the task with the higest priority
>for each job. I tried grouping but this does not appear to work.
>Thanks for the help.
>Sean M. Severson|||Roy,
That did it. I was missing the JobID comparison. Thanks so much!!
Sean M. Severson
Roy Harvey wrote:
> I think this will do it.
> SELECT *
> FROM Tasks as A
> WHERE TaskID => (SELECT TOP 1 TaskID
> FROM Tasks as B
> WHERE A.JobID = B.JobID
> ORDER BY B.Priority DESC)
> Roy Harvey
> Beacon Falls, CT
> On 17 Jan 2007 15:07:17 -0800, "NerdRunner" <sseverson@.2sts.biz>
> wrote:
> >My task table contains the id of the task, the jobid that the task is
> >associated with, and a numeric value representing the priority of the
> >task. There can be multiple tasks for a job. I need a view that
> >returns the task id, and job id of the task with the higest priority
> >for each job. I tried grouping but this does not appear to work.
> >
> >Thanks for the help.
> >
> >Sean M. Severson

Get the Report Model Fields of a particular report?

If I have a Report Path and I want to get at the fields associated with this report's Model (not the fields in the Report!!!), is there a way to do so using the ReportingService2005 or the VS.NET 2005 ReportViewer control?

I have played around with the ReportingService2005.GetItemDataSources() and the ServerReport.GetDataSources() (ReportViewer) methods, but with no luck.

I imagine that this functionality must be available becuase the Model fields are displayed in the Report Builder application once you select a Model.

Thanks!

Brian McCullough

OK...to get back to the Model do this:

New" size="2">Dim datasources() As DataSource = rs.GetItemDataSources(rptViewer.ServerReport.ReportPath)

For Each ds As DataSource In datasources
Dim modelPath As String = DirectCast(ds.Item, DataSourceReference).Reference

Dim modelItems() As ModelItem = rs.ListModelItemChildren(modelPath, Nothing, True)
For Each modelitem As ModelItem In modelItems
If item.Type = ModelItemTypeEnum.Attribute Then
'this is a field in the Model
'now how do i get the FieldType?
End If
Next

Next

Now how do I get the Field Type (i.e. String, Date, etc...)?

-Brian

Get Record Number

I have a table containing 1000 records. I do have a primary key associated with the table. But I want to get the Record Number of each record. Is there a way to get it. That means for first record it should be 1 and so on and so forth.

That depends on wheter you are using SQL Server 2005 or any version below. SQL 2k5 introduced the ROWNUMBER() function which will let your create a rownumber according to some rules (only a new number if a grouping changes, etc.) if you are using SQL2k or bwlo you will have to go another way. What are you currently using ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Currently using SQL-2000|||

There is no straight-forward solution in SQL 2000.

Basically, you can create a temp table with an identity column (record number) and insert each row of your table into it. After that, select all the rows in the temp table out with the record number.

You can also only insert the PK column into the temp table and select using a join of the temp table and the original table.

|||

I tried to get thru this problem and found out a way to do it. the query specified below can solve my purpose:

SELECT emp_id, lname, fname, job_id, (SELECT COUNT(*) FROM employee e2 WHERE e2.lname <= e.lname) AS rownumber
FROM employee e
ORDER BY lname

But again there is a catch. This particular query will work only in case I have a unique field (like lname in this case). Now I again to resolve this thing by assigning a new unique ID to each row with the use of function NEWID(). But when I am trying to use NEWID() instead of lname it is not working. I tried as

select newid() NN, e.*, (select count(*) from (select newid() NID,* from employee) e2 where e2.NID <= e.NN ) as rownumber
from employee e

it gives me an error "Invalid column name 'NN' " Can anyone help me out in sorting this error and using the NEWID() as a unique field. I do not wish to use temp table.

|||

I tried to get thru this problem and found out a way to do it. the query specified below can solve my purpose:

SELECT emp_id, lname, fname, job_id, (SELECT COUNT(*) FROM employee e2 WHERE e2.lname <= e.lname) AS rownumber
FROM employee e
ORDER BY lname

But again there is a catch. This particular query will work only in case I have a unique field (like lname in this case). Now I again to resolve this thing by assigning a new unique ID to each row with the use of function NEWID(). But when I am trying to use NEWID() instead of lname it is not working. I tried as

select newid() NN, e.*, (select count(*) from (select newid() NID,* from employee) e2 where e2.NID <= e.NN ) as rownumber
from employee e

it gives me an error "Invalid column name 'NN' " Can anyone help me out in sorting this error and using the NEWID() as a unique field. I do not wish to use temp table.