problem in Function ?

Create FUNCTION FUNCTION_NAT

(

@.F_BRANCH_CODE CHAR,

@.F_COUNTRY CHAR,

@.F_CLIENT_VALUE CHAR

)

RETURNS TABLE

AS

RETURN

(

SELECT NLGIC_VALUE AS VALUE

FROM RAGHU.NAT

WHERE BRANCH_CODE = @.F_BRANCH_CODE

AND

COUNTRY = @.F_COUNTRY

AND

CLIENT_VALUE = @.F_CLIENT_VALUE

)

This is my function . when i run it as follows

select * from FUNCTION_NAT('450','British','BRI')

i did not get any value it is empty .

But when i run this query

SELECT NLGIC_VALUE AS VALUE

FROM RAGHU.NAT

WHERE BRANCH_CODE = '450'

AND

COUNTRY = 'British'

AND

CLIENT_VALUE = 'BRI'

it works fine . What is the problem in my function .

My knee-jerk reaction is that this might be a problem with the type definitions of the function parameters; hold on and I will try to verify. Look at this:

Code Snippet

alter FUNCTION FUNCTION_NAT
(
@.F_BRANCH_CODE CHAR,
@.F_COUNTRY CHAR,
@.F_CLIENT_VALUE CHAR
)
RETURNS TABLE
AS
RETURN
( select @.f_branch_code as branch_code,
@.f_country as country,
@.f_client_value as client_value
)

go

select * from function_nat('450','British','BRI')

/*
branch_code country client_value
-- -
4 B B
*/

You need to alter your type definitions from this

Code Snippet

(
@.F_BRANCH_CODE CHAR,
@.F_COUNTRY CHAR,
@.F_CLIENT_VALUE CHAR
)

to something like this:

Code Snippet

(
@.F_BRANCH_CODE CHAR(xx),

@.F_COUNTRY CHAR(yy),
@.F_CLIENT_VALUE CHAR(zz)
)

where xx, yy, and zz are the maximum number of characters that will be passed through each argument.

|||

Kent - i think you've hit the nail on the head.

From BOL: When n is not specified in a data definition or variable declaration statement, the default length is 1

SQL will not throw an error but merely cut the string off at the length. From the look of at least one of your variables, you may be better off with the VARCHAR datatype.

HTH!

sql

Problem in between clause

Hi to all
i have a table which in which date is storing in three separate fields
all datatype char. Values storing are like
01 in day field Mar in month field and 2005 in year fields
. now when i try to get data between two dates results are not coming as
expected.
i m using following query
SELECT a.station_desc
,sum(b.ttl_appl_visited) Total_Token_Booked
,sum(b.ttl_urgent_tokens_today) Urgent_Token
FROM tbl_Value b
left JOIN dbo.Daily_Report_Station ON
dbo.tbl_line_2.Source_ID = dbo.Daily_Report_Station.Station_ID
where report_day between '15' and '07' and report_month between 'Feb'
and 'Mar'
and report_year between '2006' and '2006'
group by a.station_desc
if i give report day value like report_day between '10' and '20' it
returns me correct value but when first value is greater no result comes
as in the query
Regards,
Farid
*** Sent via Developersdex http://www.examnotes.net ***For a start something like
where report_year = '2006'
and (( report_month = 2 and report_day >= 15 ) or
( report_month = 3 and report_day < 7 ))
If you're stuck with the design you currently have, write a UDF that takes
year, month and day as arguments and returns a datetime, then use
between dbo.MyGetDate( '2006', 'Feb', '15' ) and dbo.MyGetDate( '2006',
'Mar','07')
"Ghulam Farid" wrote:

>
> Hi to all
> i have a table which in which date is storing in three separate fields
> all datatype char. Values storing are like
> 01 in day field Mar in month field and 2005 in year fields
> . now when i try to get data between two dates results are not coming as
> expected.
> i m using following query
> SELECT a.station_desc
> ,sum(b.ttl_appl_visited) Total_Token_Booked
> ,sum(b.ttl_urgent_tokens_today) Urgent_Token
> FROM tbl_Value b
> left JOIN dbo.Daily_Report_Station ON
> dbo.tbl_line_2.Source_ID = dbo.Daily_Report_Station.Station_ID
> where report_day between '15' and '07' and report_month between 'Feb'
> and 'Mar'
> and report_year between '2006' and '2006'
> group by a.station_desc
>
> if i give report day value like report_day between '10' and '20' it
> returns me correct value but when first value is greater no result comes
> as in the query
> Regards,
> Farid
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Hi,
I'd say the problem is how you use between clause.
Between translates into pair of statements >= and <=
So first part of your condition would be
where report_day >= 15 and report_day <= 7.
This condition returns false, hence all following conditions are not parsed
afaik.
try this example
select 'test' where 2 between 2 and 4
select 'test' where 3 between 4 and 2
Also, do you want data between 07.02.2006 and 15.03.2006 or between 07 and
15 day of Feb and Mar in 2006?
HTH
Peter|||yes u r right first condition is making result false so wht would b the
apropriate condition using same data structure. and its true i want
result between 07-02-2005 and 15-03-2005 but result is not coming. any
help
*** Sent via Developersdex http://www.examnotes.net ***|||You'll have to convert the months to integers. Might be a good use of a
computed column or view with a case report_month when 'Jan' then 1 etc.
Then if you want between 15 Feb and 07 Apr, after converting the months to
integers you would write something like:
where (
( month = 2 and day >= 15) or /* handle February 15-28 */
( month > 2 and month < 4 ) or /* Mar or any other months inbetween */
(month = 4 and day <= 7 ) /* handle final month Apr */
)
"Ghulam Farid" wrote:

> yes u r right first condition is making result false so wht would b the
> apropriate condition using same data structure. and its true i want
> result between 07-02-2005 and 15-03-2005 but result is not coming. any
> help
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Stop doing this and use a DATETIME data type. One of your problems is
that you are mimicking a Cobol record, which has fields for the date
components. If you understood the concept of a column -- which is
nohting like a field -- you would not make this mistake.sql

Problem converting military time from CHAR column

Hi all,

I have a time column in CHAR(4) format. The contents are stored as 'military time':

1800

1830

2130

I tried using various functions, but was not able to convert to standard time.

I need to take an existing datetime column, strip the time (00:00:00:000), convert the time column to standard and concatonate together to get the following result:

2006-05-31 06:30 PM

Any help would greatly be appreciated.

Thanks.

- gshaf

Your military time conversion wont work because you dont have a colon separating your hours and minutes.

Try this statement:
PRINT CONVERT(varchar(20), CONVERT(varchar(20), GetDate(), 101) + CONVERT(smalldatetime, '21:30'), 0)

Try this one instead, this will add a zero to your hour:

IF LEFT(RIGHT(CONVERT(datetime, '21:30', 109), 7), 1) = '1'
BEGIN
PRINT LEFT(CONVERT(varchar(20), GetDate(), 20), 10) + LTRIM(RIGHT(CONVERT(datetime, '21:30', 109), 7))
END
ELSE
BEGIN
PRINT LEFT(CONVERT(varchar(20), GetDate(), 20), 10) + ' ' + '0' + LTRIM(RIGHT(CONVERT(datetime, '21:30', 109), 7))
END

Which prints out
2006-05-31 09:30PM

Your time column is going to need that colon. Otherwise your military times will not be output properly.

|||

Worked! Just needed to correct the formatting.

Thanks so much Elliot!

- gshaf

Problem converting CHAR to DATETIME

hello all,

I am having a problem calculating the difference, in days, between two dates, STARTDATE and ENDDATE. The data is stored in the database as char(8), formatted YYYYMMDD. "Null" values are stored as '00000000'. When I try to use DATEDIFF an exception is thrown: "the conversion of a char data type to a datetime data type resulted in an out-of-range datetime value"

How can I fix this, and a get a result even if STARTDATE or ENDDATE is '00000000'? Changing the format of the stored data is not an option.

Thanks for any assistance.

Mike



CREATE TABLE dbo.DATETIME1 (
ID1 int,
STARTDATE char(8),
ENDDATE char(8)
)
INSERT into DATETIME1 (ID1, STARTDATE, ENDDATE)
VALUES (
1, '20070105', '20070108'
)
INSERT into DATETIME1 (ID1, STARTDATE, ENDDATE)
VALUES (
2, '20070105', '00000000'
)
Select * from DATETIME1
Select DATEDIFF(d, STARTDATE, Convert(datetime,ENDDATE) ) as Difference
from DATETIME1
WHERE ID1 = 1How about a case statement to check for the '00000000'? Or an isdate check?|||Ahhhh, good idea...can you help with the syntax of the CASE statement in SQA? I've done them in VB.NET, but not directly in SQL.

Thanks|||add this to your test code replacing your datediff select.


select STARTDATE, ENDDATE,
CASE
WHEN isdate(ENDDATE) = 0
THEN 0
ELSE
DATEDIFF(d, STARTDATE, Convert(datetime,ENDDATE) )
END
from DATETIME1 as DaysDifference|||THANKS! I was doing something similar, but couldn't quite get it to work! :D|||You could also try the following:
ISNULL(DATEDIFF(d, CONVERT(DATETIME, STARTDATE, 112), CONVERT(DATETIME, NULLIF(ENDDATE, '00000000'), 112), 0)
Btw: explicit conversions are always better than implicit ones:)