Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using the GetDate() Function? 1

Status
Not open for further replies.
Joined
Jun 29, 2001
Messages
195
Location
US
I'm runnning somer tests on pulling data only on current date and am having some trouble.
In one record the date is like this:

11/30/01 10:00:00 AM

Another with date like:

11/30/01

The staement below returns the second example but not the first.

select * from table1
where [timestamp] = convert(varchar(30), getdate(),101)

What am I missing, besides my sanity? 8-)

TIA Ashley L Rickards
SQL DBA
 
Will this do the trick for you?

select * from table1
where convert(varchar(30),timestamp,101 = convert(varchar(30), getdate(),101)


Rick.
 
I'm getting an error incorrect syntax near '='.

I'm having the field name changed from timestamp to callreceived. That could be causeing a problem. Ashley L Rickards
SQL DBA
 
I changed the field name but still get same error.
Any idea? Thanks. Ashley L Rickards
SQL DBA
 
Actually the return value I get is:

2001-11-30 00:00:00.000

The other field has a time. Does this have something to do with it.

Excuse my ignorance on this. Ashley L Rickards
SQL DBA
 
Is your column callreceived a DATETIME datatype?

There is a missing closing parenthesis in Mr. Cole's statement but otherwise it should work for you -

select * from table1
where convert(varchar(30),call_received,101) = convert(varchar(30), getdate(),101)




When you insert or update a DATETIME field with a string value such as '2001-11-30' without giving a value for the time, SQL Server assigns zero for the time, as if you had inserted '2001-11-30 00:00:00.000'.

This expression creates a string value from a DATETIME value -
Code:
convert(varchar(30), getdate(),101)

Today this will be 11/30/2001 because the format code 101 is just the date without the time.

Assuming timestamp, or let us say call_received, is a DATETIME column,
The query in your initial post compares a DATETIME value to a string value. Not a problem for SQL Server since it may very well be able to convert the string to a DATETIME, but the problem you will experience is that the only rows found in the query will have this date/time value -

2001-11-30 00:00:00.000

If the rows were created with actual date/time values then they will not be equal to 00:00:00.000, unless by some wild coincidence they were added at midnight!


 
Sorry Ashley, there was indeed a missing parenthesis in my statement. I was away home after I posted so didn't see your reply till today.

rac2 kindly corrected the syntax as it should be and also gives you an good explanation as to where you were going wrong.

Hopefully your all sorted on this one now.

Rick.

 
Thanks guy's ... I'll work with this.

:-V Ashley L Rickards
SQL DBA
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top