Wrox Programmer Forums
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 3rd, 2007, 08:50 PM
Authorized User
 
Join Date: Dec 2006
Posts: 70
Thanks: 0
Thanked 1 Time in 1 Post
Default Date question

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

 
Old July 3rd, 2007, 10:33 PM
Authorized User
 
Join Date: Dec 2006
Posts: 70
Thanks: 0
Thanked 1 Time in 1 Post
Default

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

 
Old July 17th, 2007, 04:39 AM
Registered User
 
Join Date: Jul 2007
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to bdyc2007
Default

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.


 
Old July 17th, 2007, 07:39 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Date/Time Sort Question Pam S Access 2 April 23rd, 2005 09:18 AM
Date of birth question satts Access 10 February 24th, 2005 11:21 AM
Date() question Tangerine Classic ASP Components 2 March 3rd, 2004 04:14 AM
Simple Date question risu VB.NET 2002/2003 Basics 7 November 12th, 2003 12:43 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.