Date creation in SQL

[wgblackmon@yahoo.com]

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_ti me_stamp' in the T_ORDER table. The
'requistion_tim e_stamp' file is of type 'datetime'.

Below is pseudo_code for what I need to do:

T_ORDER.requisi tion_time_stamp >= Date(ToNumber(S chool Year),6,1)
AND
T_ORDER.requisi tion_time_stamp < Date(ToNumber(S chool 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(Schoo l 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!

[Erland Sommarskog]

Re: Date creation in SQL

wgblackmon@yaho o.com (wgblackmon@yah oo.com) writes:[color=blue]
> 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_ti me_stamp' in the T_ORDER table. The
> 'requistion_tim e_stamp' file is of type 'datetime'.
>
> Below is pseudo_code for what I need to do:
>
> T_ORDER.requisi tion_time_stamp >= Date(ToNumber(S chool Year),6,1)
> AND
> T_ORDER.requisi tion_time_stamp < Date(ToNumber(S chool Year + 1) , 6,
> 1)[/color]

It's as simple as:

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

YYYYMMDD is one of the few date formats that are always interpreted
the same in SQL Server.
[color=blue]
> 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.[/color]

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


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[MGFoster]

Re: Date creation in SQL

-----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.requisiti on_time_stamp >= @school_year + '0601'
AND ORDER.requisiti on_time_stamp < CAST(CAST(@scho ol_year as int)+1 as
char(4)) + '0601'

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

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

iQA/AwUBRFkvk4echKq OuFEgEQJROwCfRj D+rzabv+WXmUD22 z4RtRczyy4An1eh
sCSLmD3GVmLEk9R TEtCoPQo3
=TuKz
-----END PGP SIGNATURE-----


wgblackmon@yaho o.com wrote:[color=blue]
> 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_ti me_stamp' in the T_ORDER table. The
> 'requistion_tim e_stamp' file is of type 'datetime'.
>
> Below is pseudo_code for what I need to do:
>
> T_ORDER.requisi tion_time_stamp >= Date(ToNumber(S chool Year),6,1)
> AND
> T_ORDER.requisi tion_time_stamp < Date(ToNumber(S chool 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(Schoo l 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!
>[/color]