How to get records (In this week) only?

Hi,

How to get records from MSSQL database which only recorded from the biging of this week?

in example my week begin from Sunday..

You might want to be more specific. Is this for a single table or for all tables? Does/do your table/s have timestamp columns?|||

Only for one table, and yes the table have timestamp column.

I'm also trying to use this query to get the number of records inserted on this month:

SELECT COUNT(*)

FROM MyTable

WHERE DateTimeColumn > MONTH(1)

It doesnt return the correct value for records inserted on Feb!!

|||

You're comparing against a constant - MONTH(1)

Try this instead:

WHERE MONTH(DateTimeColumn) > 1

Note, though, that this may not be optimal on large tables, since the function wrapped around DateTimeColumn will likely prevent an index to be used, if there is any on that column..

/Kenneth

|||

Thanks KeWin, this format is working perfectly..

Whats the optimal solution for Larg Tables?

And what about (In this week)? how can I get it?

|||Hi,

SELECT*FROM

SomeTable

WHEREDATENAME(wk,SomeDateColumn)=DATENAME(wk,GETDATE())AND

YEAR(SomeDateColumn)=YEAR(GETDATE())

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens K. Suessmeyer,

it worked :)

|||

Weeknumbers are probably the trickiest part of a date.

While this works well for US-style weeknumbers, just be aware that it doesn't work as well for those of us who use the ISO week numbering scheme. That is, weeknumbers are dependant on which country's calendar it's applyed against.

/Kenneth