How to get todays date in format YY/MM/DD and to compare it to another date passed into th

I need to do the following and am hoping someone can help me out.

I have C#(asp.net app) that will call a stored procedure. The C# will pass in a date to the
stored procedure. The date is in the format YY/MM/DD. Once inside of the stored procedure, the date
passed into the stored proc needs to be compared to todays date. Todays date must be determined in
the SQL.

So basically here is my pseudo code for what I am trying to accomplish. Basically I just am after
the comparison of the two values:

If @.BeginDate < TodaysDate

The difficult part is how to obtain the value for "TodaysDate"

Taking into consideration that "TodaysDate" should probably be in the format of YY/MM/DD considering that is how the date it is to be compared with is being passed in.

Can someone please code this out for me in Microsoft SQL. I would be forever grateful.

I figured out what I needed to know, but will have further questions and will need help. Thanks to all.

how to get this output ?

Hi Everybody,
I have a Table Call Referrals like this
EmpId Cl_Date RefrlNo OptNo
1 NULL 2565 2222
1 01/01/2007 2565 2223
2 01/02/2007 2567 2230
3 01/02/2007 2568 2231
3 01/03/2007 2568 2232
4 NULL 2569 2245
4 NULL 2570 2246

From this table i need to get the Emp Id, Cl_date RefrlNO,OptNo
where EmpId must have repeated more than one time as well as it must have at least one Cl_Date + at least one NULL Cl_Date

after all these requirement satisfy , i need to pick again a row data where Cl_Date Is null ?

So the out put should be like this
EmpId Cl_Date RefrlNo OptNo
1 NULL 2565 2222

how do i do this task ?

any idea ?
regards
suis

Code Snippet

createtable Referrals

(

EmpId int,

Cl_Date datetime,

RefrlNo int,

OptNo int

)

GO

insertinto Referrals values(1,NULL, 2565, 2222)

insertinto Referrals values(1,'01/01/2007', 2565, 2223)

insertinto Referrals values(2,'01/02/2007', 2567, 2230)

insertinto Referrals values(3,'01/02/2007', 2568, 2231)

insertinto Referrals values(3,'01/03/2007', 2568, 2232)

insertinto Referrals values(4,NULL,2569,2245)

insertinto Referrals values(4,NULL,2570,2246)

SELECT EmpId, Cl_date, RefrlNo, OptNo FROM Referrals

WHERE

EmpID IN(select EmpId from Referrals where Cl_date isNULL)

AND

EmpID IN(select EmpId from Referrals where Cl_date isNOTNULL)

AND

Cl_date ISNULL

If for EmpId we have line where Cl_date is NULL (see first IN clause) and line where Cl_Date is not NULL (see second IN clause), its that we have at least two lines with same EmpID

How to get this out put ?

Hi everybody
I have a requirement like this
I have a table call Child which looks like following

ChildId ChildAgeByYear ReferralSoure NoOfReferral
Ch01 02 Self 2
Ch01 02 Open 1
Ch03 02 Self 1

Now my problem is I need to display the above Child table information like this

AgeGroup ReferralSoure NoOfReferral
0 0
1 0
2 Self 3
Open 1
3 0
4 0

I need to count the child age group by child age like, 1, 2, 3, 4, and their ReferralSource and NoOfReferral for each child depends on the ReferralSource

Any Idea ?

Regards
Suis

Perhaps something like this:

declare @.child table
( ChildId varchar(5),
ChildAgeByYear integer,
ReferralSource varchar(12),
NoOfReferral integer
)
insert into @.child
select 'Ch01', 2, 'Self', 2 union all
select 'Ch01', 2, 'Open', 1 union all
select 'Ch03', 2, 'Self', 1
--select * from @.child

select AgeGroup,
isnull(ReferralSource, '') as ReferralSource,
sum( case when NoOfReferral is null then 0
else NoOfReferral end
) as NoOfReferral
from ( select 0 as AgeGroup union all select 1 union all
select 2 union all select 3 union all select 4
) as n
left join @.child c
on n.AgeGroup = c.ChildAgeByYear
group by AgeGroup,
ReferralSource
order by AgeGroup,
sum( case when NoOfReferral is null then 0
else NoOfReferral end
) desc

/*
AgeGroup ReferralSource NoOfReferral
-- --
0 0
1 0
2 Self 3
2 Open 1
3 0
4 0
*/

|||Thank you very much
4 your quick response,i will let u now the outcome once i test this
best regards
suis

How to get this out put ?

Hi everybody I have a table call Child
and the information like this

ChildId Department Born_Dttm
01 ICU 01/01/2007
02 NormatWard 01/01/2006
03 ICU 01/01/2005

I need to get this out put like i need to count how many child attached to a particular department depending on child age ?

Department < 5 Months > 5Months 1 Year 2Year
ICU 1 0 0 1
NormatWard 0 0 1 0

Age calculation should be up to todays date ?

Any Idea ?
regards
suis

Here are a couple of resources detailing how to do this.

Pivot Tables - How to rotate a table in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;175574

Pivot Tables -Dynamic Cross-Tabs
http://www.sqlteam.com/item.asp?ItemID=2955

|||Hi Arni
thank you for u r quick response and many thanks to u r link
actually i have learned some thing out of that link and i could produce some SQL
Bellow is my SQL

DECLARE @.Child TABLE

(

[ChildId] varchar(2),

[Department] varchar(10),

[Born_Dttm] varchar(12)

)

INSERT INTO @.Child([ChildId], [Department], [Born_Dttm])

SELECT '01', 'ICU', '01/01/2007' UNION ALL

SELECT '02', 'NormalWard', '01/01/2006' UNION ALL

SELECT '03', 'ICU', '01/01/2005'

select [Department] ,

(select count(datediff(mm,[Born_Dttm],getdate())/12) from @.Child ch1 where datediff(mm,[Born_Dttm],getdate())/12 < 1 AND ch1.[Department]=ch.[Department] ) as ZeroToOneYear,
(select count(datediff(mm,[Born_Dttm],getdate())/12) from @.Child ch2 where datediff(mm,[Born_Dttm],getdate())/12 >= 1 AND datediff(mm,[Born_Dttm],getdate())/12 < 2 AND ch2.[Department]=ch.[Department] ) as OneTOTwoYear,
(select count(datediff(mm,[Born_Dttm],getdate())/12) from @.Child ch2 where datediff(mm,[Born_Dttm],getdate())/12 >= 2 AND datediff(mm,[Born_Dttm],getdate())/12 < 3 AND ch2.[Department]=ch.[Department] ) as TwoTOThreeYear
from @.Child ch
GROUP BY ch.[Department]

Now the problem is i can't get the Months calculation ?
can u help me to get months calculation
like 0 to 5 months ? and 5 to 1 year ?
Any Idea ?
regards
suis|||

This might work more efficiently. (Note the ZeroToOneYear is double counting the Months columns.)

Code Snippet

DECLARE @.Child TABLE
( [ChildId] varchar(2),
[Department] varchar(10),
[Born_Dttm] varchar(12)
)

INSERT INTO @.Child VALUES ( '01', 'ICU', '01/01/2007' )
INSERT INTO @.Child VALUES ( '02', 'NormalWard', '01/01/2006' )
INSERT INTO @.Child VALUES ( '03', 'ICU', '01/01/2005' )

SELECT
[Department],
[Under 5 Months] = sum( CASE WHEN datediff( month, Born_Dttm, getdate() ) < 5 THEN 1 ELSE 0 END ),
[5 MonthsToOneYear] = sum( CASE WHEN datediff( month, Born_Dttm, getdate() ) BETWEEN 5 AND 12 THEN 1 ELSE 0 END ),
[ZeroToOneYear] = sum( CASE datediff( year, Born_Dttm, getdate() ) WHEN 0 THEN 1 ELSE 0 END ),
[OneToTwoYear] = sum( CASE datediff( year, Born_Dttm, getdate() ) WHEN 1 THEN 1 ELSE 0 END ),
[TwoTOThreeYear] = sum( CASE datediff( year, Born_Dttm, getdate() ) WHEN 2 THEN 1 ELSE 0 END )
from @.Child ch
GROUP BY ch.[Department]

Department Under 5 Months 5 MonthsToOneYear ZeroToOneYear OneToTwoYear TwoTOThreeYear
- -- -- - --
ICU 1 0 1 0 1
NormalWard 0 0 0 1 0

|||Try this:

select [Department],

sum(case when age_mos < 5 then 1 else 0 end) as '< 5 months',

sum(case when age_mos > 4 and age_mos < 12 then 1 else 0 end) as '> 5 months',

sum(case when age_mos > 11 and age_mos < 24 then 1 else 0 end) as '1 year',

sum(case when age_mos > 23 then 1 else 0 end) as '2 year'

from

(select [Department], datediff(mm, [Born_Dttm], getdate()) as age_mos

from @.child) as t

group by [Department]

|||Thanks everybody for this valuable comments
now i could manage to sort out with the help of this forum
this is great help for me,
and this is the place to learn ........

many thanks.......to MSDN forum members ..........

regards
suis

How to get this out put !

Hi i have a table call Employee
Emp_Id Area_Code Temp_Tran_Id
02 2CL NULL
02 2CL 01235
03 3NY NULL
03 2CL 1452
08 2CL NULL

I need to get this out put from that table data !

Emp_Id Area_Code Temp_Tran_Id
02 2CL 01235
03 3NY NULL
03 2CL 1452
08 2CL NULL

These are the rules i must follow when getting that out put

Emp_Id and the Area_Code Cannot be repeated same time ,

if it's repeated i need to get the record where Temp_Tran_Id is not null

Any Idea ?

Code Snippet

select Emp_Id, Area_Code, max(Temp_Tran_Id) as TT_Id
from Employee
group by Emp_Id, Area_Code

But what you have to do when there's several records with the same Emp_Id and Area_Code and in some of them Temp_Tran_Id is not null?|||

Perhaps something like this:

declare @.employee table
( Emp_id varchar(5),
Area_Code char(3),
Temp_Tran_Id varchar(12)
)

insert into @.employee
select '02', '2CL', null union all
select '02', '2CL', '01235' union all
select '03', '3NY', null union all
select '03', '2CL', '1452' union all
select '08', '2CL', null

select Emp_id,
Area_code,
max (Temp_Tran_Id) as Temp_Tran_Id
from @.employee
group by Emp_Id,
Area_Code
order by Emp_Id,
max (Temp_Tran_Id), ''

/*
Emp_id Area_code Temp_Tran_Id
02 2CL 01235
03 3NY NULL
03 2CL 1452
08 2CL NULL
*/

|||Thanks very much itsw working perfectly now!
thanks every body
cheers

How to get Procedure call text from within SQL Stored Procedure

Hi,
I'm using SQL Server 2005.
I have implemented some SPs with the TRY..CATCH structure and each time
that I have an error, I just log it into a dedicated LogError table.
I would like to add in LogError a column containing the command that
was used by the user (containing the Procedure Name and the calling
parameters)
Is there any way to perform it ?
Thanks
PFI
have a look at
http://www.nigelrivett.net/SQLAdmin/sp_nrInfo.html
The bits with
dbcc inputbuffer
and
fn_get_sql
Get the sql executed if it is available.
dbcc inputbuffer is more likely to have the user command.
Neither is guaranteed.
www.nigelrivett.net
*** Sent via Developersdex http://www.codecomments.com ***
|||Thanks a lot.
dbcc inputbuffer was exactly what I was looking for.
PFI
nigelrivett a crit :

> have a look at
> http://www.nigelrivett.net/SQLAdmin/sp_nrInfo.html
> The bits with
> dbcc inputbuffer
> and
> fn_get_sql
> Get the sql executed if it is available.
> dbcc inputbuffer is more likely to have the user command.
> Neither is guaranteed.
> www.nigelrivett.net
>
> *** Sent via Developersdex http://www.codecomments.com ***

How to get Procedure call text from within SQL Stored Procedure

Hi,
I'm using SQL Server 2005.
I have implemented some SPs with the TRY..CATCH structure and each time
that I have an error, I just log it into a dedicated LogError table.
I would like to add in LogError a column containing the command that
was used by the user (containing the Procedure Name and the calling
parameters)
Is there any way to perform it ?
Thanks
PFIhave a look at
http://www.nigelrivett.net/SQLAdmin/sp_nrInfo.html
The bits with
dbcc inputbuffer
and
fn_get_sql
Get the sql executed if it is available.
dbcc inputbuffer is more likely to have the user command.
Neither is guaranteed.
www.nigelrivett.net
*** Sent via Developersdex http://www.developersdex.com ***|||Thanks a lot.
dbcc inputbuffer was exactly what I was looking for.
PFI
nigelrivett a =E9crit :
> have a look at
> http://www.nigelrivett.net/SQLAdmin/sp_nrInfo.html
> The bits with
> dbcc inputbuffer
> and
> fn_get_sql
> Get the sql executed if it is available.
> dbcc inputbuffer is more likely to have the user command.
> Neither is guaranteed.
> www.nigelrivett.net
> > > *** Sent via Developersdex http://www.developersdex.com ***

How to get Procedure call text from within SQL Stored Procedure

Hi,
I'm using SQL Server 2005.
I have implemented some SPs with the TRY..CATCH structure and each time
that I have an error, I just log it into a dedicated LogError table.
I would like to add in LogError a column containing the command that
was used by the user (containing the Procedure Name and the calling
parameters)
Is there any way to perform it ?
Thanks
PFIhave a look at
http://www.nigelrivett.net/SQLAdmin/sp_nrInfo.html
The bits with
dbcc inputbuffer
and
fn_get_sql
Get the sql executed if it is available.
dbcc inputbuffer is more likely to have the user command.
Neither is guaranteed.
www.nigelrivett.net
*** Sent via Developersdex http://www.codecomments.com ***|||Thanks a lot.
dbcc inputbuffer was exactly what I was looking for.
PFI
nigelrivett a =E9crit :

> have a look at
> http://www.nigelrivett.net/SQLAdmin/sp_nrInfo.html
> The bits with
> dbcc inputbuffer
> and
> fn_get_sql
> Get the sql executed if it is available.
> dbcc inputbuffer is more likely to have the user command.
> Neither is guaranteed.
> www.nigelrivett.net
>=20
>=20
> *** Sent via Developersdex http://www.codecomments.com ***

How to Get Error Output from and OLE DB Command Destination

I have a data flow that takes an OLE DB Source, transforms it and then uses an OLE DB Command as a destination. The OLE DB Command executes a call to a stored procedure and I have the proper wild cards indicated. The entire process runs great and does exactly what is intended to do.

However, I need to know when a SQL insert fails what record failed and I need to log this in a file somewhere. I added a Flat File Destination object and configured appropriately. I created 3 column names for the headers in the flat file and matched them with column names existing for output. When I run this package the flat file log is created ok, but no data is ever pumped into the file when a failure of the OLE DB Command occurs.

I checked the Advanced Editor for the OLE DB Command object and under the OLE DB Command Error Output node on the Input and Output Properties tab I notice that the ErrorCode and ErrorColumn output columns both have ErrorRowDisposition set to RD_NotUsed. I would guess this is the problem and why no data is written to my log file, but I cannot figure out how to get this changed (fields are greyed out so no access).

Any help would be greatly appreciated.

To get rows down the error output you change the ErrorRowDisposition property for the input to be redirect row. Have you done this? If not go the last page of the Advanced Editor, select the Input, and change the ErrorRowDisposition property.|||

I reviewed your suggestion of changing the ErrorRowDisposition value to RD_RedirectRow and that is where the issue is. I view the Advanced Editor for the OLE DB Command destination object and expand the Input Columns under OLE DB Command Input and see several input columns. However, the problem is every one of those columns has an ErrorRowDisposition=RD_NotUsed and the field is greyed out so I am unable to change the setting. Would I need to change any settings in the source or data conversion objects to allow these values to be editable?

|||Select the input and stop there, don't expand the columns. The setting is on the input which is in effect the parent for the columns.