Select not returning records based on Date I am using

Select not returning records based on Date I am using

Post by Gerry Viato » Sun, 30 Mar 2003 02:40:32



Hi all,

I have datetime stored like this: 2003-03-28 12:29:45.043
             it's insert by Trigger like this: set Datetimecolumn =
getdate()

I try and retreive like this:
      SELECT * from  TableName where Datetimecolumn = '2003-3-28'

returns no rows, but that is wrong. What am I doing wrong?
I'm sure it has  something to do with time that is stored.

thanks for any help

Gerry

 
 
 

Select not returning records based on Date I am using

Post by Rakesh Chadh » Sun, 30 Mar 2003 02:48:30


Yes, it has.

Use this

Select * from TableName where convert(char(8),DateTimeColumn112)='2003-3-28'

or

Select * from TableName where DateTimeColumn between Cast('2003-3-28' as
datetime) and Cast('2003-3-28' as datetime)+1

Rakesh


Quote:> Hi all,

> I have datetime stored like this: 2003-03-28 12:29:45.043
>              it's insert by Trigger like this: set Datetimecolumn =
> getdate()

> I try and retreive like this:
>       SELECT * from  TableName where Datetimecolumn = '2003-3-28'

> returns no rows, but that is wrong. What am I doing wrong?
> I'm sure it has  something to do with time that is stored.

> thanks for any help

> Gerry


 
 
 

Select not returning records based on Date I am using

Post by David Porta » Sun, 30 Mar 2003 02:51:20


DATETIME columns store both date and time. When you specify '2003-03-28' the
time defaults to midnight so your query is equivalent to:

SELECT * from  TableName where Datetimecolumn = '2003-03-28 00:00:00.000'

To get the correct result, either specify the full time, or, if you want all
rows for a particular date:

SELECT * from  TableName where Datetimecolumn >='2003-03-28' AND
Datetimecolumn<'2003-03-29'

--
David Portas
------------
Please reply only to the newsgroup
--


Quote:> Hi all,

> I have datetime stored like this: 2003-03-28 12:29:45.043
>              it's insert by Trigger like this: set Datetimecolumn =
> getdate()

> I try and retreive like this:
>       SELECT * from  TableName where Datetimecolumn = '2003-3-28'

> returns no rows, but that is wrong. What am I doing wrong?
> I'm sure it has  something to do with time that is stored.

> thanks for any help

> Gerry

 
 
 

Select not returning records based on Date I am using

Post by rayda » Sun, 30 Mar 2003 02:53:47


Quote:> I'm sure it has  something to do with time that is stored.

Yup

SELECT * from  TableName where Datetimecolumn >= '2003-3-28' and Datetimecolumn < '2003-3-29'


Quote:> Hi all,

> I have datetime stored like this: 2003-03-28 12:29:45.043
>              it's insert by Trigger like this: set Datetimecolumn =
> getdate()

> I try and retreive like this:
>       SELECT * from  TableName where Datetimecolumn = '2003-3-28'

> returns no rows, but that is wrong. What am I doing wrong?
> I'm sure it has  something to do with time that is stored.

> thanks for any help

> Gerry

 
 
 

Select not returning records based on Date I am using

Post by David Porta » Sun, 30 Mar 2003 02:59:45


Should be
SELECT * FROM TableName WHERE CONVERT(CHAR(8),DateTimeColumn,112)='20030328'

But this query cannot be optimised by an index seek which may slow things
down significantly.

--
David Portas
------------
Please reply only to the newsgroup
--


Quote:> Yes, it has.

> Use this

> Select * from TableName where

convert(char(8),DateTimeColumn112)='2003-3-28'

> or

> Select * from TableName where DateTimeColumn between Cast('2003-3-28' as
> datetime) and Cast('2003-3-28' as datetime)+1

> Rakesh



> > Hi all,

> > I have datetime stored like this: 2003-03-28 12:29:45.043
> >              it's insert by Trigger like this: set Datetimecolumn =
> > getdate()

> > I try and retreive like this:
> >       SELECT * from  TableName where Datetimecolumn = '2003-3-28'

> > returns no rows, but that is wrong. What am I doing wrong?
> > I'm sure it has  something to do with time that is stored.

> > thanks for any help

> > Gerry

 
 
 

Select not returning records based on Date I am using

Post by Gerry Viato » Sun, 30 Mar 2003 03:01:43


Thanks all.

Gerry


Quote:> Hi all,

> I have datetime stored like this: 2003-03-28 12:29:45.043
>              it's insert by Trigger like this: set Datetimecolumn =
> getdate()

> I try and retreive like this:
>       SELECT * from  TableName where Datetimecolumn = '2003-3-28'

> returns no rows, but that is wrong. What am I doing wrong?
> I'm sure it has  something to do with time that is stored.

> thanks for any help

> Gerry

 
 
 

Select not returning records based on Date I am using

Post by Gerry Viato » Sun, 30 Mar 2003 03:30:51


Ok, again thanks

   one more question

This has a specific date in the query, how about if I want to specify
getnow() for the current date.

SELECT * from  patientclinical where (dateofex >= '2003-03-28' AND dateofex
<'2003-03-29') or
                          (Amendtime >='2003-03-28' AND Amendtime
<'2003-03-29')

thanks
Gerry


Quote:> Hi all,

> I have datetime stored like this: 2003-03-28 12:29:45.043
>              it's insert by Trigger like this: set Datetimecolumn =
> getdate()

> I try and retreive like this:
>       SELECT * from  TableName where Datetimecolumn = '2003-3-28'

> returns no rows, but that is wrong. What am I doing wrong?
> I'm sure it has  something to do with time that is stored.

> thanks for any help

> Gerry

 
 
 

Select not returning records based on Date I am using

Post by Gerry Viato » Sun, 30 Mar 2003 03:47:17


GetDate()

sorry

Gerry


> Ok, again thanks

>    one more question

> This has a specific date in the query, how about if I want to specify
> getnow() for the current date.

> SELECT * from  patientclinical where (dateofex >= '2003-03-28' AND
dateofex
> <'2003-03-29') or
>                           (Amendtime >='2003-03-28' AND Amendtime
> <'2003-03-29')

> thanks
> Gerry



> > Hi all,

> > I have datetime stored like this: 2003-03-28 12:29:45.043
> >              it's insert by Trigger like this: set Datetimecolumn =
> > getdate()

> > I try and retreive like this:
> >       SELECT * from  TableName where Datetimecolumn = '2003-3-28'

> > returns no rows, but that is wrong. What am I doing wrong?
> > I'm sure it has  something to do with time that is stored.

> > thanks for any help

> > Gerry

 
 
 

Select not returning records based on Date I am using

Post by Gerry Viato » Sun, 30 Mar 2003 04:32:13


Ok how about this seams to work fine,




cast(datepart(mm,getdate())as varchar) + '-' + cast(datepart(dd,getdate())as
varchar)

cast(datepart(mm,getdate())as varchar) + '-' + cast(datepart(dd,getdate()+
1)as varchar)



Is there a shorter better way to do this? Why does the datepart function not
take yymmdd ? seams easy enough
    for MS to have encluded that functionality.

Thanks
Gerry


> GetDate()

> sorry

> Gerry



> > Ok, again thanks

> >    one more question

> > This has a specific date in the query, how about if I want to specify
> > getnow() for the current date.

> > SELECT * from  patientclinical where (dateofex >= '2003-03-28' AND
> dateofex
> > <'2003-03-29') or
> >                           (Amendtime >='2003-03-28' AND Amendtime
> > <'2003-03-29')

> > thanks
> > Gerry



> > > Hi all,

> > > I have datetime stored like this: 2003-03-28 12:29:45.043
> > >              it's insert by Trigger like this: set Datetimecolumn =
> > > getdate()

> > > I try and retreive like this:
> > >       SELECT * from  TableName where Datetimecolumn = '2003-3-28'

> > > returns no rows, but that is wrong. What am I doing wrong?
> > > I'm sure it has  something to do with time that is stored.

> > > thanks for any help

> > > Gerry

 
 
 

Select not returning records based on Date I am using

Post by Jeff William » Sun, 30 Mar 2003 10:15:36


Instead of using CAST - try using CONVERT which allows you to specify the
style returned.  Use the following to return the date in YYYYMMDD format:

    CONVERT( char(8), <datecolumn>, 112)

Look in BOL for further information.

Jeff


> Ok how about this seams to work fine,




> cast(datepart(mm,getdate())as varchar) + '-' +

cast(datepart(dd,getdate())as
> varchar)

> cast(datepart(mm,getdate())as varchar) + '-' + cast(datepart(dd,getdate()+
> 1)as varchar)





> Is there a shorter better way to do this? Why does the datepart function
not
> take yymmdd ? seams easy enough
>     for MS to have encluded that functionality.

> Thanks
> Gerry



> > GetDate()

> > sorry

> > Gerry



> > > Ok, again thanks

> > >    one more question

> > > This has a specific date in the query, how about if I want to specify
> > > getnow() for the current date.

> > > SELECT * from  patientclinical where (dateofex >= '2003-03-28' AND
> > dateofex
> > > <'2003-03-29') or
> > >                           (Amendtime >='2003-03-28' AND Amendtime
> > > <'2003-03-29')

> > > thanks
> > > Gerry



> > > > Hi all,

> > > > I have datetime stored like this: 2003-03-28 12:29:45.043
> > > >              it's insert by Trigger like this: set Datetimecolumn =
> > > > getdate()

> > > > I try and retreive like this:
> > > >       SELECT * from  TableName where Datetimecolumn = '2003-3-28'

> > > > returns no rows, but that is wrong. What am I doing wrong?
> > > > I'm sure it has  something to do with time that is stored.

> > > > thanks for any help

> > > > Gerry

 
 
 

Select not returning records based on Date I am using

Post by Bill Hollinshead [MSFT » Sun, 30 Mar 2003 18:31:49


Hi Gerry,

Re "seams easy enough for  MS to have encluded that functionality".
CAST is an ANSI SQL-92 function, which means its functionality is not
defined by Microsoft <g>. In contrast, CONVERT is a Microsoft function, and
thus CONVERT has that functionality (as is seen in Jeff's correct usage).

Thanks,

Bill Hollinshead
Microsoft, SQL Server

This posting is provided "AS IS" with no warranties, and confers no
rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

 
 
 

Select not returning records based on Date I am using

Post by Gerry Viato » Tue, 01 Apr 2003 23:32:14


Thanks

Gerry



Quote:> Hi Gerry,

> Re "seams easy enough for  MS to have encluded that functionality".
> CAST is an ANSI SQL-92 function, which means its functionality is not
> defined by Microsoft <g>. In contrast, CONVERT is a Microsoft function,
and
> thus CONVERT has that functionality (as is seen in Jeff's correct usage).

> Thanks,

> Bill Hollinshead
> Microsoft, SQL Server

> This posting is provided "AS IS" with no warranties, and confers no
> rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

 
 
 

1. Select previous record based on date time

I have a table that contain invoice information as follows

InvoiceID    int
FromDt      datetime
ToDt          datetime
CreateDt    datetime
...

I would like to have a query where I get the InvoiceID that was immediately
prior to a current invoiceID (basically, need a view that returns the
invoice ID and its first previous invoice ID). Note that there can be
multiple Invoice records with the same FromDt and ToDt, but were created at
different points in time (hence the CreateDt/InvoiceID would be different).

Is there any way of doing this?

Eg: If the table contains data like
       InvoiceID   |              FromDt                |
ToDt                 |   CreateDt
020500000122 | 2002-01-01 00:00:00.000 | 2002-01-31 00:00:00.000 |
2002-02-19 17:11:03.000
020310000001 | 2001-12-01 00:00:00.000 | 2001-12-31 00:00:00.000 |
2002-01-31 09:40:59.000
013520000437 | 2001-12-01 00:00:00.000 | 2001-12-31 00:00:00.000 |
2001-12-18 12:59:00.000

I would like the view to return
CurrentInvID        PrevInivID
020500000122    020310000001

Thanks,
Girish

2. SQL 6.5 sp5a with hotfix performance degrading....

3. Select condition Not returning correct Records?

4. Text Centering problem in FPW

5. SQL SELECT not returning all records

6. Access Password doesnt recognize by VB?

7. Returning randomly selected records using DAO

8. Relationship between sql server , local machine and Domain groups and ids

9. Selecting Records not included in a date range from another table

10. Using ADO to Read a Text File Does not Return First Record

11. Dropdown box from value list not returning records when used with Web Companion

12. ODBC Drive returns records using MDAC 2.0 but not MDAC 2.1 SP 2

13. Between 2 dates not returning data for the end date