Problem getting Recoreds from Store Proedure Between Dates...

i have a gridView and i want to get the recoreds between specific dates.. so i put two calendars to select the dates...

and they will filter the recoreds..(i seleced dates and it returns non recors and theres recored betwenn those dates and in a SQL View it works )

What can i do? Thanks

Store Proceure Function:

CREATE PROCEDUREsp_FindTnoaGrid@.SearchTnoanvarchar(14),@.beginDateas nvarchar(50),@.endDateas nvarchar(50)AS-- order by dbo.V_tnuot.t_erech desc--if (@.beginDate='%')or (@.endDate='%')SELECT dbo.V_tnuot.kod_lakoha, dbo.V_tnuot.t_erech, dbo.V_tnuot.t_peula, dbo.V_tnuot.scum_peulaassum, dbo.V_tnuot.strHpoalimSugKodTnua, dbo.V_tnuot.DescSugPeula,cast(year(cast(@.beginDateas datetime))as nvarchar)+'-'+cast(month(cast(@.beginDateas datetime))as nvarchar)+'-'+cast(day(cast(@.beginDateas datetime))as nvarchar)as try, dbo.V_tnuot.Hpoalim_HodeshSaharFROM dbo.V_tnuotLEFTOUTER JOIN dbo.tblCategTnuot_KodPeulaON dbo.V_tnuot.strHpoalimSugKodTnua = dbo.tblCategTnuot_KodPeula.idKodTnuaWHERE ( kod_lakoha = @.SearchTnoa)if (@.beginDate<>'%')and (@.endDate<>'%')SELECT dbo.V_tnuot.kod_lakoha, dbo.V_tnuot.t_erech, dbo.V_tnuot.t_peula, dbo.V_tnuot.scum_peulaassum, dbo.V_tnuot.strHpoalimSugKodTnua, dbo.V_tnuot.DescSugPeula, dbo.V_tnuot.Hpoalim_HodeshSaharFROM dbo.V_tnuotLEFTOUTER JOIN dbo.tblCategTnuot_KodPeulaON dbo.V_tnuot.strHpoalimSugKodTnua = dbo.tblCategTnuot_KodPeula.idKodTnuaWHERE ( kod_lakoha = @.SearchTnoa)and (dbo.V_tnuot.t_peulaBETWEENcast(@.beginDateas datetime)andcast(@.endDateas datetime) )GO

What happens if you call the stored procedure directly e.g. from a query window in SQL Server Management Studio, passing in the appropriate parameters? Do you get the results you expect?

Are you tripping up over the classic "time" issue - DateTime fields don't just have a date, they have a time component so "2007-08-21" is not the same as "2007-08-21 14:23:08" etc.

Problem converting UTC dates in dts

Can anyone help me convert UTC dates in a dts package? I have a db I need to transform, but all the dates are in utc format. Is there a function I can use in an ActiveX transformation script to convert, say, 38166, to a valid datetime value? There seems to be almost no documentation in using utc in SQL.How is that a UTC time?

http://www.dxing.com/utcgmt.htm|||I'll try...but for the record i dont think 38166 is a valid UTC date.
Please correct me if i'm wrong.

here is what i thougght UTC date looked like:

2004-07-01T16:15:30

SQL server CAST and CONVERT will handle this for you, by default.|||But your example does somewhat resemble a Julian date...|||But your example does somewhat resemble a Julian date...
1938?

or 2038?

But yeah it looks more julian..|||My vote is for 2008, since the Unix epoch is based on 1970.

-PatP|||I'll go with thta...(since I wrote a udf for that one once)...god damn toolbox...got to clean this damn thing out...

Wait a minute...|||i bet there'll be a party or 2 ~3:15 on Jan 19 2038

the end of the world as we know it.

:D|||you've got a julian date converting UDF?

Oooooo...i've wanted to do that but never had a project that required it.

Care to share??
:)|||I wrote one ages ago for military style Julian dates, and just posted it a day or two ago. Check here (http://www.dbforums.com/t1003180.html) for details.

-PatP|||thanks much.

sorry for the thread hijack chunky,
did you get a decent answer for the original post?

perhaps you could post an example of the date string you are dealing with, if not?

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:)

Problem converting C application from using DBLIB bcp to using ODBC bcp.

I have an application that was converted from using DBLIB bcp to using ODBC bcp. All other field/data types bind are updated correctly, but dates put in the date

1753-01-01 00:00:00.000 when it should be NULL. The variable is a char [24] and the first item is set to \0 ( dbLoanBankruptcy.MotionforReliefReqDate[0] = NullChar; ).

Is there any example or way to put the date in as null when the variable is null and an actual date as needed?

bcp_bind(LoanBankruptcyDBPPtr, (BYTE *)&dbLoanBankruptcy.MotionforReliefReqDate, 0, 23, NULL, 0, SQLCHARACTER, 13)

Thank you,

Joel

Following site maybe helpful:

http://msdn2.microsoft.com/en-us/library/aa198011(sql.80).aspx

|||The link above only discusses dates enough to get an actual date into a table, but does not discuss how to get a NULL date stored. Is there a way to save <NULL>?

Would it be wrong to use the bcp_bind statement you needed for each row/record and re-bind each time? As in...

if(dbLoanBankruptcy.MotionforReliefFiledDate[0]==NullChar) {
if (bcp_bind(LoanBankruptcyDBPPtr, (BYTE *)&dbLoanBankruptcy.MotionforReliefFiledDate,0, SQL_NULL_DATA, NULL, 0, SQLVARCHAR, 14) == FAIL) LogBindError("LoanBankruptcy", "MotionforReliefFiledDate",LoanBankruptcyDBPPtr);
}else{
if (bcp_bind(LoanBankruptcyDBPPtr, (BYTE *)&dbLoanBankruptcy.MotionforReliefFiledDate,0, 23, NULL, 0, SQLVARCHAR, 14) == FAIL) LogBindError("LoanBankruptcy", "MotionforReliefFiledDate",LoanBankruptcyDBPPtr);
}

|||

You don't need to rebind each time

http://msdn2.microsoft.com/en-gb/library/aa177853(SQL.80).aspx

struct

{

int iIndicator;

char szDatetime[30];

} urBCPData;

if (bcp_bind(om_hDbc2, (LPCBYTE) &urBCPData, 4, SQL_VARLEN_DATA , NULL, 0,SQLVARCHAR, 1) == FAIL)

{

// Raise error and return.

return;

}

// Insert NULL

urBCPData.iIndicator = SQL_NULL_DATA;

if (bcp_sendrow(om_hDbc2) == FAIL)

{

// Raise error and return.

return;

}

// Insert 1753-01-01 00:00:00.000

urBCPData.iIndicator = 23;

strcpy(urBCPData.szDatetime, "1753-01-01 00:00:00.000");

if (bcp_sendrow(om_hDbc2) == FAIL)

{

// Raise error and return.

ShowSQLError(om_hDbc2, om_hStmt2);

return;

}

|||Tested and works... thanks.

The normal bind statement but the data portion is now (byte *)&data.datetime_struct

Code Snippet

if(dbLoanBankruptcy.MotionforReliefReqDate[0]==NullChar) {
dbLoanBankruptcy.bounddate.iIndicator = SQL_NULL_DATA;
}else{
dbLoanBankruptcy.bounddate.iIndicator = 24;
strcpy(dbLoanBankruptcy.bounddate.iValue, dbLoanBankruptcy.MotionforReliefReqDate);
}