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!