Date Format Data Driven Subscription

I'm getting the typical error that the format of my date parameters is not correct. Although i'm getting my dates in a select from a datetime field.

Is there really no universal format that can be delivered to RS that he can interprete the date right, whatever language the report has been set to.

Kind Regards .....

You can define your datetime parameters as YYYY-MM-DDTHH:MM:SS. This format is independent of the language of the report.

See http://msdn2.microsoft.com/en-us/library/ms159825.aspx for moer details.

Date creation in SQL

Hi,
I have a parameter that is being input as a String via Crystal Reports.
It's called 'School Year'
and a typical value is '2006'. I need to be able to create a 'Date'
that is compared to a field value in a where clause. In this case the
db field is called 'requisition_time_stamp' in the T_ORDER table. The
'requistion_time_stamp' file is of type 'datetime'.

Below is pseudo_code for what I need to do:

T_ORDER.requisition_time_stamp >= Date(ToNumber(School Year),6,1)
AND
T_ORDER.requisition_time_stamp < Date(ToNumber(School Year + 1) , 6,
1)

The 'Date' method is a mock-up and is problematic because the 'School
Year' field needs to be converted to a number so it can be incremented
in the second statement, so I also need a conversion method, something
like 'ToNumber(School Year)' .

I've looked at the Date functions in the Sybase documentation and I
don't see anything that fits what I'm trying to do.

Any help would be greatly appreciated!wgblackmon@.yahoo.com (wgblackmon@.yahoo.com) writes:
> I have a parameter that is being input as a String via Crystal Reports.
> It's called 'School Year'
> and a typical value is '2006'. I need to be able to create a 'Date'
> that is compared to a field value in a where clause. In this case the
> db field is called 'requisition_time_stamp' in the T_ORDER table. The
> 'requistion_time_stamp' file is of type 'datetime'.
> Below is pseudo_code for what I need to do:
> T_ORDER.requisition_time_stamp >= Date(ToNumber(School Year),6,1)
> AND
> T_ORDER.requisition_time_stamp < Date(ToNumber(School Year + 1) , 6,
> 1)

It's as simple as:

T_ORDER.requisition_time_stamp >= @.year + '0101'
T_ORDER.requisition_time_stamp < dateadd(DAY, 1, @.year + '1231')

YYYYMMDD is one of the few date formats that are always interpreted
the same in SQL Server.

> I've looked at the Date functions in the Sybase documentation and I
> don't see anything that fits what I'm trying to do.

Sybase? This newsgroup is for Microsoft SQL Server. Thankfully, the
solution above works for Sybase as well.

--
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|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1

You could do something like this (in a stored procedure):

-- the INPUT parameter would be

@.school_year CHAR(4)

-- then use this in the WHERE clause

ORDER.requisition_time_stamp >= @.school_year + '0601'
AND ORDER.requisition_time_stamp < CAST(CAST(@.school_year as int)+1 as
char(4)) + '0601'

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRFkvk4echKqOuFEgEQJROwCfRjD+rzabv+WXmUD22z4RtR czyy4An1eh
sCSLmD3GVmLEk9RTEtCoPQo3
=TuKz
--END PGP SIGNATURE--

wgblackmon@.yahoo.com wrote:
> Hi,
> I have a parameter that is being input as a String via Crystal Reports.
> It's called 'School Year'
> and a typical value is '2006'. I need to be able to create a 'Date'
> that is compared to a field value in a where clause. In this case the
> db field is called 'requisition_time_stamp' in the T_ORDER table. The
> 'requistion_time_stamp' file is of type 'datetime'.
> Below is pseudo_code for what I need to do:
> T_ORDER.requisition_time_stamp >= Date(ToNumber(School Year),6,1)
> AND
> T_ORDER.requisition_time_stamp < Date(ToNumber(School Year + 1) , 6,
> 1)
> The 'Date' method is a mock-up and is problematic because the 'School
> Year' field needs to be converted to a number so it can be incremented
> in the second statement, so I also need a conversion method, something
> like 'ToNumber(School Year)' .
> I've looked at the Date functions in the Sybase documentation and I
> don't see anything that fits what I'm trying to do.
> Any help would be greatly appreciated!

Date Conversion

I'm searching on a smalldatetime field in SQL Server so a typical value would be 09/21/2005 11:30:00 AM. I have a search form which offers the user a textbox to search by date and unless they enter the exact date and time, no matching records are found. Of course I want I all records for a given day to be returned. This is how I'm doing it now. Thanks.

Dim dteDate_RequestedAsString = txtDate_Requested.Text

If dteDate_Requested <>""Then
strSqlText +=" Date_Requested='" & dteDate_Requested &"'"
EndIf

You have to change to DateTime to get the results you want because SmallDateTime have limited resolution. Try the link below for more info. Hope this helps.
http://www.stanford.edu/~bsuter/sql-datecomputations.html|||I've changed my SQL Server field type from SmallDateTime to DateTime and it's still not working. If I do a response.write on the SQL statement, I see that it's working correctly (WHERE Date_Requested='09/22/2005')|||You need to keep in mind that there is no Date data type. All ofthe data types involving dates also includes times. You need towind up with a query that looks like this:

WHERE Date_Requested >= '20050922' AND Date_Requested < '20050923'

That is my best recommendation. That will return you all recordswhere Date_Requested falls on 9/22/2005 regardless of the time part ofthe date you are storing.
I must strongly recommend to you that you use Parameters instead of concatenating UI-supplied data to a string to be executed.
Here's the why:

Please, please, please, learn about injection attacks!
How To: Protect From SQL Injection in ASP.NET

And here's the how:

Using Parameterized Query in ASP.NET, Part 1
Using Parameterized Query in ASP.NET, Part 2

|||I see. I've been a developer quite awhile and did not know this was the best way to search date fields.
This is for a small intranet app so I'm not concerned about SQL injection attacks in this case but that is very good advice.
Thanks for the help.
|||

evanburen wrote:

I see. I've been a developer quite awhile anddid not know this was the best way to search date fields.

It's just what I've learned through trial and error and seeing otherpeople struggling with it. The method I suggested will takeadvantage of any index on your date field, and it takes the time partof the date out of the equation.

evanburen wrote:

This isfor a small intranet app so I'm not concerned about SQL injectionattacks in this case but that is very good advice.

I have a few thoughts to offer on this viewpoint.
While one would like to think that all coworkers are trustworthy,a curious or disgruntled employee, or perhaps a temporary contractor,might try to access data to which they are not otherwise privileged, orperhaps even attempt to inflict damage to the database ornetwork. There still might be data which needs to be keptsafeguarded, such as payroll information, benefit information.