no case sensitive

Hi,
Does anybody know how to let the database treat my data as no case
sensitive?
For example: I have a customer type field, when I retrieve it from the
mainframe, it has "retail" or "RETAIL" as differently typed by users. But
when I run a report for customer type "Retail", I want to them to be showing
as one type on the report.
How do I do this?
Thanks,
SarahUse the lower function to convert the column to lowercase.
i.e.
Select *
from Orders
where lower(OrderType) = 'retail'
"Sarah G." <sguo@.coopervision.com> wrote in message
news:uMv8Wv#GEHA.1076@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Does anybody know how to let the database treat my data as no case
> sensitive?
> For example: I have a customer type field, when I retrieve it from the
> mainframe, it has "retail" or "RETAIL" as differently typed by users. But
> when I run a report for customer type "Retail", I want to them to be
showing
> as one type on the report.
> How do I do this?
> Thanks,
> Sarah
>|||Are you using SQL Server 2000? If so, look up the COLLATE keyword in BOL.
Here's a very simple example:
create table #a(mychar varchar(20) collate sql_latin1_general_cp1_cs_as)
go
insert #a values('aBc')
insert #a values('abC')
go
select distinct mychar from #a
go
select distinct mychar collate sql_latin1_general_cp1_ci_as from #a
go
"Sarah G." <sguo@.coopervision.com> wrote in message
news:uMv8Wv#GEHA.1076@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Does anybody know how to let the database treat my data as no case
> sensitive?
> For example: I have a customer type field, when I retrieve it from the
> mainframe, it has "retail" or "RETAIL" as differently typed by users. But
> when I run a report for customer type "Retail", I want to them to be
showing
> as one type on the report.
> How do I do this?
> Thanks,
> Sarah
>sql

no case sensitive

Hi,
Does anybody know how to let the database treat my data as no case
sensitive?
For example: I have a customer type field, when I retrieve it from the
mainframe, it has "retail" or "RETAIL" as differently typed by users. But
when I run a report for customer type "Retail", I want to them to be showing
as one type on the report.
How do I do this?
Thanks,
Sarah
Use the lower function to convert the column to lowercase.
i.e.
Select *
from Orders
where lower(OrderType) = 'retail'
"Sarah G." <sguo@.coopervision.com> wrote in message
news:uMv8Wv#GEHA.1076@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Does anybody know how to let the database treat my data as no case
> sensitive?
> For example: I have a customer type field, when I retrieve it from the
> mainframe, it has "retail" or "RETAIL" as differently typed by users. But
> when I run a report for customer type "Retail", I want to them to be
showing
> as one type on the report.
> How do I do this?
> Thanks,
> Sarah
>

Next Friday

I have a table called Order, within which there is a field called orderdate,
I can I program so that I can always retrieve order with an order date as
next Friday when I am running this script anytime this w.
Thanks,
QjleeHi
You can work this out using a calendar table see
http://www.aspfaq.com/show.asp?id=2519 or use the dataadd and datepart
functions (see books online).
John
"qjlee" wrote:

> I have a table called Order, within which there is a field called orderdat
e,
> I can I program so that I can always retrieve order with an order date as
> next Friday when I am running this script anytime this w.
> Thanks,
> Qjlee|||You can solve it by using the DATEPART() function like this:
declare @.today smalldatetime
set @.today = '20051102'
create table #tmp
(
ID int identity(1,1) primary key clustered,
OrderDate smalldatetime null
)
set nocount on
insert into #tmp (OrderDate) values ('20051101')
insert into #tmp (OrderDate) values ('20051101')
insert into #tmp (OrderDate) values ('20051102')
insert into #tmp (OrderDate) values ('20051103')
insert into #tmp (OrderDate) values ('20051103')
insert into #tmp (OrderDate) values ('20051104')
insert into #tmp (OrderDate) values ('20051104')
insert into #tmp (OrderDate) values ('20051104')
insert into #tmp (OrderDate) values ('20051107')
insert into #tmp (OrderDate) values ('20051108')
insert into #tmp (OrderDate) values ('20051108')
insert into #tmp (OrderDate) values ('20051109')
insert into #tmp (OrderDate) values ('20051110')
insert into #tmp (OrderDate) values ('20051110')
insert into #tmp (OrderDate) values ('20051111')
insert into #tmp (OrderDate) values ('20051111')
insert into #tmp (OrderDate) values ('20051111')
set nocount off
select * from #tmp
where OrderDate =
(
select min(OrderDate) from #tmp -- Earliest...
where datepart(dw,OrderDate) = 6 -- Friday...
and OrderDate >= @.today -- on or after "today"
)
drop table #tmp
The DATEPART() function, when used with the 'dw' parameter, returns the
day of the w and is dependant on how you have the SET DATEFIRST
setting configured. For me, Friday = 6. Also, if your OrderDate column
contains time info as well then you'll need to do a little range
checking to make sure the OrderDate is somewhere on that day (and not
just at midnight) - there are heaps of references on how to do that (I'm
sure I've seen an example on http://aspfaq.com).
*mike hodgson*
blog: http://sqlnerd.blogspot.com
qjlee wrote:

>I have a table called Order, within which there is a field called orderdate
,
>I can I program so that I can always retrieve order with an order date as
>next Friday when I am running this script anytime this w.
>Thanks,
>Qjlee
>|||Try this:
SELECT CURRENT_TIMESTAMP + (6-DATEPART(dw,'2005-11-02'))
Mark Graveline
Take The Challenge
http://www.sqlchallenge.com
--|||Qjlee,
This should always give you the Friday in the w following
the current one, assuming your w begins on Monday.
dateadd(d,datediff(d,'19000101',getdate(
))/7*7+11,'19000101')
Steve Kass
Drew University
qjlee wrote:

>I have a table called Order, within which there is a field called orderdate
,
>I can I program so that I can always retrieve order with an order date as
>next Friday when I am running this script anytime this w.
>Thanks,
>Qjlee
>|||Try this instead:
SELECT CURRENT_TIMESTAMP + (6-DATEPART(dw,CURRENT_TIMESTAMP))
Thanks,
M. E. Houston
"SQLChallenge" <sqlchallenge@.saikoconsulting.com> wrote in message
news:1131062936.928610.144390@.g47g2000cwa.googlegroups.com...
> Try this:
> SELECT CURRENT_TIMESTAMP + (6-DATEPART(dw,'2005-11-02'))
> --
> Mark Graveline
> Take The Challenge
> http://www.sqlchallenge.com
> --
>