scheduleDate BETWEEN 20/5/2007 AND 30/5/2007 no rows returned - Database Discussion Boards

Re: Using Strongly typed Datasets and a Data Access Layer

Paul Conrad1-Jun-07 10:14

1-Jun-07 10:14

Colin Angus Mackay wrote:
Drathmar wrote:
Does anyone know a good way of preventing the DAL from being bypassed?

It is called discipline.

Sometimes the short answers are the best Big Grin | :-D

Can't search through Date

Gamzun1-Jun-07 5:52

Gamzun

1-Jun-07 5:52

im using this query

select date from table where date='5/7/2007'

it returns '0' rows, but when I run this follwoing query it returns 2 rows

select date from table where date='5/7/2007 1:30:00 PM'

i don't want to search with time how can i do that?

i even try this

select date from table where date='5/7/2007%'

but it gives error as it cant convert smalldatetime to string any solution.

Re: Can't search through Date

leckey1-Jun-07 6:14

leckey

1-Jun-07 6:14

http://sqlserver2000.databases.aspfaq.com/can-i-make-sql-server-format-dates-and-times-for-me.html[^]

I've used this reference for formating in the past. Hope it helps.

__________________
Bob is my homeboy.

Re: Can't search through Date

Mike Dimmick1-Jun-07 8:03

Mike Dimmick

1-Jun-07 8:03

Everyone seems to be doing this today!

Firstly - use ISO 8601 format. This format - yyyyMMdd - is not subject to misinterpretation by different locale settings.

Secondly, dates have a time part. If you want any time on 7 May 2007, use:

WHERE date >= '20070507' AND date < '20070508'

If you don't specify a time part for a date literal, the time part is set to 00:00:00 (i.e. 12am).

Stability. What an interesting concept. -- Chris Maunder

Re: Can't search through Date

Gamzun1-Jun-07 21:47

Gamzun

1-Jun-07 21:47

Thanks Mike,
I'll be much oblige if you can tell me how to set that format? n where should set this ISO format either in OS or MSSQL Management Studio?
Thanks Again

Re: Can't search through Date

Rob Graham2-Jun-07 4:50

Rob Graham

2-Jun-07 4:50

gamzun wrote:
n where should set this ISO format either in OS or MSSQL Management Studio?

YOU must employ the format in YOUR application / queries. This is not an OS or Server task. It is up to the application to format user entered dates properly before submitting them to the server in queries....

Re: Can't search through Date

P_pk3-Jun-07 19:39

P_pk

3-Jun-07 19:39

Use convert statement to the format required and use style 105 to cut date...
Remember to convert date to the table format while selecting... '%' Sign can be used only with a string and if ur date format in the table is datetime then that cant be done

PPK

scheduleDate BETWEEN 20/5/2007 AND 30/5/2007 no rows returned

steve_rm1-Jun-07 3:03

steve_rm

1-Jun-07 3:03

Hello

SQL Server 2005

I have tried and cannot return any rows either though there are records that should be returned, using the sql below.

SELECT * FROM Calls WHERE clientName IN ('Bankside Engineering Limited') <br />
AND scheduleDate BETWEEN 20/5/2007 AND 30/5/2007

In the database scheduleDate is a dateTime datatype.

In the database I have copied and pasted.
23/05/2007 00:00:00

I tried the following

SELECT * FROM Calls WHERE clientName IN ('Bankside Engineering Limited') <br />
AND scheduleDate BETWEEN '20/5/2007' AND '30/5/2007'

And got an error message:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Many thanks for any help with this,

Steve

Re: scheduleDate BETWEEN 20/5/2007 AND 30/5/2007 no rows returned

Mike Dimmick1-Jun-07 3:17

Mike Dimmick

1-Jun-07 3:17

Your connection is probably set to US English. This seems to be the default.

Never use a local time format with SQL Server. They're just too prone to confusion and different interpretation depending on the locale. Instead, use the ISO 8601 date format - yyyyMMdd.

So I would write

SELECT * FROM Calls WHERE 
   clientName IN ('Bankside Engineering Limited') 
AND
   scheduleDate BETWEEN '20070520' AND '20070530'

Stability. What an interesting concept. -- Chris Maunder

Re: scheduleDate BETWEEN 20/5/2007 AND 30/5/2007 no rows returned

steve_rm1-Jun-07 3:28

steve_rm

1-Jun-07 3:28

Hello,

Thanks for your response.

I tried this

SELECT * FROM Calls WHERE clientName IN ('Bankside Engineering Limited') <br />
AND ScheduleDate BETWEEN '20070521' AND '20070531'

No error messages but no rows returned.

I have this date below stored in the database, but this should have been returned.
23/05/2007 00:00:00

Many thanks,

Steve

Re: scheduleDate BETWEEN 20/5/2007 AND 30/5/2007 no rows returned

kubben1-Jun-07 6:20

kubben

1-Jun-07 6:20

Well then I would guess that record doesn't have a clientName = 'Bankside Engineering Limited'
Why did you use the IN since there is only one name?

Ben

Re: scheduleDate BETWEEN 20/5/2007 AND 30/5/2007 no rows returned

steve_rm4-Jun-07 6:08

steve_rm

4-Jun-07 6:08

Hello Ben,

In my visual basic code the user will select from a list of companies. They could select all or just select a couple of them. My query in my visual basic will string format the array of companies. Just to keep it simple I just put one in there.

The problem is corrected with the date between. I now know that the sql server uses US formatting by default.

Thanks,

Steve

Re: scheduleDate BETWEEN 20/5/2007 AND 30/5/2007 no rows returned

itwillgetbetter14-Jun-07 3:01

itwillgetbetter

14-Jun-07 3:01

Try this out
BETWEEN CONVERT(DATETIME, '2004-06-21 00:00:00', 102) AND CONVERT(DATETIME,
'2004-06-25 00:00:00', 102))

http://msdn2.microsoft.com/en-us/library/aa226054(sql.80).aspx

Best common connection type

gavthegenius31-May-07 22:52

gavthegenius

31-May-07 22:52

Hi,

currently tryin 2 find a connection from .net that can work with both sql server and windows ce as the application i am creating will need to access both? any words of wisdom would be great, was told maybe an ole db one might work but not too sure now,

thanks
gav

primary key and foreign key

saravanan0531-May-07 18:47

saravanan05

31-May-07 18:47

hi friends

i have two tables like t1,t2

In t1,i set primary key
In t2,i set foreign key

now both tables are related.

now i am update ,delete primary key column but it is not affected foreginkey table

how to write query affcted primarykey table as well as foregin key table

can anybody help me

regards
saravan

Re: primary key and foreign key

sam#31-May-07 19:07

sam#

31-May-07 19:07

make cascade update on

SetFieldValue

hero199531-May-07 17:32

hero1995

31-May-07 17:32

What's wrong with these code: a new field named "Field19" is created. i wanna set all the values in Field19 are "abc:

CDaoDatabase db;
CDaoTableDef tableDef(&db);
db.Open("C:\\ktt.mdb");
tableDef.Open("Table3");
CDaoRecordset recset( &db );

tableDef.CreateField("Field19",dbText,255,dbUpdatableField | dbVariableField);

if ( !recset.IsEOF() )
recset.MoveLast();

while( !recset.IsEOF() )
{

recset.SetFieldValue("Field19","abc");

recset.MovePrev();

}

Re: SetFieldValue

Mike Dimmick1-Jun-07 3:20

Mike Dimmick

1-Jun-07 3:20

You should call Edit before starting to change a record's values and call Update when done, before moving to the next record.

Stability. What an interesting concept. -- Chris Maunder

Error with SQL Server 2005 Endpoints

duaatul31-May-07 6:19

duaatul

31-May-07 6:19

I am unable to 'add webreference' of a web service written as a SQL Server 2005 HTTP EndPoint in my VS 2005.
It gives an error - 'There was an error while downloading http://server/servicename?wsdl

Though:
1. I can open the wsdl in IE on my machine.
2. On other machine on VS 2005, I can download the wsdl and add it as a web reference.
3. I can add a web reference of an HTTP endpoint hosted on my local SQL Server.

So what could be wrong. Is there any configuration required in the SQL Server to allow my IP address access to download the proxy?

Thanks

Fuzzy Grouping performance issue

seee sharp31-May-07 6:18

seee sharp

31-May-07 6:18

Hello All,

We have a SSIS package which includes Fuzzy Grouping in Data Flow. It takes two columns from source table and saves outputs in different table with match score etc. Following is the way we are doing it:

1. Load required data from table using OLEDB connection (source)

2. Sort the data

3. Apply Fuzzy grouping (using dedicated database instead tempdb and MinSimilarity = 0.6)

4. Send to destination table using OLEDB connection (destination)

In input table we have millions of records. It takes too long to execute and even sometime it fails after running 12 hours. Any suggestions for performance improvement are welcomed.

Appreciate your help.

- Ashish

Convert SQL datetime to just date

kozu31-May-07 5:29

kozu

31-May-07 5:29

Hi,
I need to convert the DateTime to a date format. I know I have to use Convert, but I don't think I use it right because I'm not getting the expected result.
Here's my scenarion: I want to do a sum for all products in the DB that were bought during a given period of time. My query looks like this

select convert(datetime,convert(char(10),OrderDate,101)), SUM(Qty)
from Orders
where convert(datetime,convert(char(10),OrderDate,101)) between '05/29/05' and '06/10/05'
group by convert(datetime,convert(char(10),OrderDate,101))
order by convert(datetime,convert(char(10),OrderDate,101))

the result looks like this:

OrderDate Qty
2005-05-30 00:00:00.000 1
2005-05-31 00:00:00.000 4

How can I get rid of the 0s at the end of OrderDate?
thanx

Re: Convert SQL datetime to just date

kubben31-May-07 5:36

kubben

31-May-07 5:36

You can change your where clause to be:
where OrderDate >= '2005-05-29' and OrderDate <= '2005-06-10 23:59:59'

Hope that helps.
Ben

Re: Convert SQL datetime to just date

kozu31-May-07 6:01

kozu

31-May-07 6:01

Kubben,
Thanks for the reply, but changing the where clause didn't work. I need to change the 'convert' part of the select in order to alter how the data is being displayed, but to what?

I tried casting it to char, and it partially fixed my problem:

select cast(OrderDate as char(11)), SUM(Qty)
from Orders
where convert(datetime,convert(char(10),OrderDate,101)) between '05/29/05' and '06/10/05'
group by cast(OrderDate as char(11))
order by cast(OrderDate as char(11))

The result is:

Jun 1 2005 2
Jun 2 2005 1
May 30 2005 1

but now the problem is that the ordering will be done alphabetically and not by date.
stuck again Frown | :(

Re: Convert SQL datetime to just date

Shahil Shah31-May-07 6:13

Shahil Shah

31-May-07 6:13

Hi
If you are viewing the data in a datagrid you can use the following data formating expression:
{0:dd/MM/yyyy} without the need to use the convert staement you are using

otherwise try this:

SET DATEFORMAT mdy
select CAST(OrderDate as varchar(11)) as OrderDate, SUM(Qty)
where OrderDate between '05/29/05' and '06/10/05'
group by OrderDate
order by OrderDate

Shahil

Re: Convert SQL datetime to just date

kozu31-May-07 6:24

kozu

31-May-07 6:24

Shahil,
The 'order by' will do the sorting in alphabetical order, since now OrderDate is a string, so I'll get 'June' before 'May'.
I need to be able to still sort by date.

thanx

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.