Date question

kscase · July 3rd, 2007, 08:50 PM

I think I'm going to be spending a lot of time in this forum. WOW!

Anyway, on to my question/issue ...

I'm using SQL2K5, but I don't think that really matters in the entire scheme of things.

I'm trying to find out how many records were added to one of my tables today. Each record has a column, date_insert, that is populated with the current date and time.

I ran some simple queries this evening to get my record counts. This is what I got back. Obviously, I'm finding the results a little strange, or I wouldn't be posting this.

Code:

/*------------------------
declare @total int,
        @before int,
        @after int
select @total=count(*) from Equipment
select @before=count(*) from Equipment
    where date_insert < '7/3/2007'
select @after=count(*) from Equipment
    where date_insert > '7/2/2007'
print 'Total rows:  '+cast(@total as varchar)
print 'Rows before: '+cast(@before as varchar)
print 'Rows after:  '+cast(@after as varchar)
------------------------*/
Total rows:  34828
Rows before: 34086
Rows after:  2310

Does anyone have an explanation and/or suggestion for how to get the 'correct' answer?

Thanks,
Karen

kscase · July 3rd, 2007, 10:33 PM

I found my answer, indirectly looking at lots of messages on this forum.

This is how I resolved it:

Code:

declare @total int,
        @before int,
        @after int,
        @dtInputDate1 datetime,
        @dtInputDate2 datetime
set @dtInputDate1='2007/07/03'
set @dtInputDate2='2007/07/02'

--CONVERT(VARCHAR(10), date_insert, 111)

select @total=count(*) from Equipment
select @before=count(*) from Equipment
    where CONVERT(VARCHAR(10), date_insert, 111) < @dtInputDate1
select @after=count(*) from Equipment
    where CONVERT(VARCHAR(10), date_insert, 111) > @dtInputDate2
print 'Total rows:  '+cast(@total as varchar)
print 'Rows before: '+cast(@before as varchar)
print 'Rows after:  '+cast(@after as varchar)
------------------------*/
Total rows:  34828
Rows before: 34086
Rows after:  742

I got the idea from www.sql-server-helper.com.
I had never visited the site before, so I'm learning lots of new things. (And I thought I was at least semi-experienced.)

Thanks,
Karen

bdyc2007 · July 17th, 2007, 04:39 AM

And I also think you can use below:

declare @total int,
        @before int,
        @after int,
        @dtInputDate1 datetime,
        @dtInputDate2 datetime
set @dtInputDate1='20070703'
set @dtInputDate2='20070702'

select @total=count(*) from Equipment
select @before=count(*) from Equipment
    where date_insert < @dtInputDate1
select @after=count(*) from Equipment
    where date_insert > @dtInputDate2
print 'Total rows: '+cast(@total as varchar)
print 'Rows before: '+cast(@before as varchar)
print 'Rows after: '+cast(@after as varchar)

Your question is about the format of datetime.

happygv · July 17th, 2007, 07:39 AM

Karen,

I think you don't need the CONVERT(VARCHAR(10), date_insert, 111) part in the code. It should work without that in your case. And doing a convert on the column still works and slows the performance.

_________________________
- Vijay G
Strive for Perfection