Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

TIMESTAMP column Expand / Collapse
Author
Message
Posted Tuesday, June 17, 2008 12:27 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 14, 2009 10:11 PM
Points: 475, Visits: 364
I have a Customers table with a TIMESTAMP column Date_Registered. I only see 'Binary Data' as values in this column when I open this table.

I need to query this table to return all Applications that were registered between 2007 and 2008.

How do I do this?

SELECT * FROM dbo.Customers
WHERE Date_Received BETWEEN '1/1/2007' AND '12/31/2008'

Gives me the following error:
Implicit conversion from data type varchar to timestamp is not allowed. Use the CONVERT function to run this query.

SELECT * FROM dbo.Customers
WHERE CONVERT(NVARCHAR,Date_Received) BETWEEN '1/1/2007' AND '12/31/2008'

Gives me the following error:
Explicit conversion from data type timestamp to nvarchar is not allowed.

SELECT * FROM dbo.Customers
WHERE Date_Received BETWEEN CONVERT(TIMESTAMP,'1/1/2007') AND CONVERT(TIMESTAMP,'12/31/208')

as well as
SELECT * FROM dbo.Customers
WHERE CONVERT(DATETIME,Date_Received) BETWEEN '1/1/2007' AND '12/31/2008'

Returns absolutely no records.
Post #518034
Posted Tuesday, June 17, 2008 1:11 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, April 13, 2014 11:32 AM
Points: 939, Visits: 778
The timestamp data type doesnt preserve time or date data. It is for versioning a row and is automatically populated when an insert or update takes place.

Take a look at this MSDN page for more info: http://msdn.microsoft.com/en-us/library/ms182776.aspx

Thanks,

Matt.
Post #518043
Posted Tuesday, June 17, 2008 1:17 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 14, 2009 10:11 PM
Points: 475, Visits: 364
Right! Thank you so much!

Which means.. there's no way I can query this table since there's no Datetime field here!
Post #518045
Posted Tuesday, June 17, 2008 3:34 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, April 13, 2014 11:32 AM
Points: 939, Visits: 778
Im afraid so.

And i think the TIMESTAMP data type name is a bit misleading.

Matt.
Post #518087
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse