By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,281 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

Date creation in SQL

P: n/a
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!

May 3 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
wg********@yahoo.com (wg********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 3 '06 #2

P: n/a
-----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-----
wg********@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!

May 3 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.