help on Date handling ?

Dear all,
What is the way under sql to extract the month from a date and then defined
a querry whcih fetch only data based on that particular month ?
thaks for your help
regardsSomething like this ?
declare @.dateVar datetime
select @.dateVar = '10102004'-- or whatever
select <column list>
from table1
where month(@.DateVar) = month(dateColumn)
MC
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:EAF2309E-1EE2-4830-9FB7-41B3896A7257@.microsoft.com...
> Dear all,
> What is the way under sql to extract the month from a date and then
> defined
> a querry whcih fetch only data based on that particular month ?
> thaks for your help
> regards|||examnotes (sergecalderara@.discussions.microsoft.com)
writes:
> What is the way under sql to extract the month from a date and then
> defined a querry whcih fetch only data based on that particular month ?
One way is:
SELECT .. FROM tbl
WHERE datecol >= convert(char(6), @.date, 112) + '01'
AND datecol < convert(char(6), dateadd(Month, 1, @.date), 112) + '01'
There are ways to write this in shorter code, but it's important to not
put the date column into any expression, as that would preclude the
use of index on that column.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi
To get a particular month from the given date u can use:
SELECT MONTH(<given_date> )
FROM <TABLE>
or
SELECT DATEPART("m",<given_date> )
FROM <TABLE>
to get records based on a particular date.
SELECT *
FROM <TABLE>
WHERE DATEPART("m",<given_date> ) = <month>
Please let me know if you have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"serge calderara" wrote:

> Dear all,
> What is the way under sql to extract the month from a date and then define
d
> a querry whcih fetch only data based on that particular month ?
> thaks for your help
> regards

help needed in error handling and undo transaction

I am reading a temptable, and doing 2 inserts. In case of error, i want the 2 inserts to be undone, and move to the next line. The complete opposite is happening and the process is being stopped while i wanr it to move on!Help appreciated!

This is my code:

BEGIN TRANSACTION

if exists(select [id] from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#textfile'))

drop table #textfile

CREATE TABLE #textfile (line varchar(8000))

BULK INSERT #textfile FROM 'c:\init_newsl.txt'

DECLARE table_cursor CURSOR FOR SELECT line FROM #textfile

OPEN table_cursor FETCH NEXT FROM table_cursor INTO @.oneline

SET XACT_ABORT ON

WHILE (@.@.FETCH_STATUS = 0 AND @.oneline != '')

BEGIN

INSERT INTO mytable1 values(@.f1, @.f2)

IF @.@.ERROR <> 0

BEGIN

PRINT 'Error in insertion of table1. Error is ' + LTRIM(STR(@.@.ERROR))

RAISERROR('',15,1)

goto next_line

END

INSERT INTO mytable2 values(@.f3, @.f4)

IF @.@.ERROR <> 0

BEGIN

PRINT 'Error in insertion of table2. Error is ' + LTRIM(STR(@.@.ERROR))

RAISERROR('',15,1)

goto next_line

END

goto next_line

next_line:

FETCH NEXT FROM table_cursor INTO @.oneline

END /* while fetch status = 0 */

Hi Terry,

You need to begin a transaction for each unit of work that you with to either commit or rollback. In your case, you are encapsulating the entire process in the transaction by placing your begin outside of the individual fetch statements. Also, I can't see a commit/rollback anywhere.

I would question your need to use a cursor here - can you post what you're trying to do and maybe we can help?

Anyway, if you did want to go down the cursor route, you would need to:

WHILE (@.@.FETCH_STATUS = 0 AND @.oneline != '')
BEGIN
BEGIN TRANSACTION t1

INSERT INTO MyTable1...

IF (@.@.ERROR <> 0)
BEGIN
ROLLBACK t1
GOTO NextLine
END

...etc

NextLine:
IF (@.@.TRANCOUNT >= 1) -- or >= 2 if you've a parent tran...
COMMIT t1

FETCH...
END

Cheers,
Rob