Convert DBTimeStamp to Date

Hopefully this is a quick question and that has a quick answer...just because I'm a newb.

I want to run a query with a select statement that where todays date (or any date for that matter equals the timestamp column.

Ex:

SELECT ID FROM SOMETABLE WHERE MyColumnDateTimeStamp = '4/4/2006'
Anyone know how to format this statement correctly?

Much appreciated.
LVL 6
carmodykAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

StephenCairnsCommented:
format both dates to be the same format that should fix any issues
0
StephenCairnsCommented:
SELECT ID FROM SOMETABLE WHERECONVERT(CHAR(24), MyColumnDateTimeStamp ,113)=CONVERT(CHAR(24), '4/4/2006',113)

gives dd mm yyyy
0
StephenCairnsCommented:
actually 113 gives the time also
I think that should have been 103
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

appariCommented:

i think timestamp and datetime datatypes are totally different types. timestamp datatype is actually binary(8) type and i think you cannot compare them.
0
appariCommented:
more on timestamp datatype from MSDN

Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a timestamp column within the database. This counter is the database timestamp. This tracks a relative time within a database, not an actual time that can be associated with a clock. A table can have only one timestamp column. Every time that a row with a timestamp column is modified or inserted, the incremented database timestamp value is inserted in the timestamp column. This property makes a timestamp column a poor candidate for keys, especially primary keys. Any update made to the row changes the timestamp value and, therefore, changes the key value. If the column is in a primary key, the old key value is no longer valid, and foreign keys referencing the old value are no longer valid. If the table is referenced in a dynamic cursor, all updates change the position of the rows in the cursor. If the column is in an index key, all updates to the data row also generate updates of the index.

You can use the timestamp column of a row to easily determine whether any value in the row has changed since the last time it was read. If any change is made to the row, the timestamp value is updated. If no change is made to the row, the timestamp value is the same as when it was previously read. To return the current timestamp value for a database, use @@DBTS.

0
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT ID FROM urTable WHERE
CONVERT(CHAR(8), urColumn ,1)='4/27/2006'  

SELECT ID FROM urTable WHERE
CONVERT(CHAR(8), urColumn ,3)='27/4/2006'  

FYI


select CONVERT(varchar(8),getdate(),3)  --dd/mm/yy
select CONVERT(varchar(8),getdate(),1)  --mm/dd/yy
0
carmodykAuthor Commented:
I appreciate all the input, wow, this site is really cool!   However, I need to be very clear about my request as I was looking to be able to convert the DBTimeStamp to a DateTime because I also wanted to be able to run a query where I can select ID's placed between certain dates, not just one date.  For example:

SELECT ID FROM SOMETABLE WHERE (MyColumnDateTimeStamp >= '4/4/2006') AND (MyColumnDateTimeStamp <= '4/8/2006' )

I've increased the points because of this.
0
Aneesh RetnakaranDatabase AdministratorCommented:
hope you didn't c my post


SELECT ID FROM SOMETABLE
WHERE  MyColumnDateTimeStamp >=  cast('4/4/1996' as datetime)
 AND  MyColumnDateTimeStamp <=  cast('4/8/2006' as datetime)

0
carmodykAuthor Commented:
Sorry Aneeshattingal, but the Query didn't produce any results.  The DBTimeStamp I refer too is that of a TimeStamp datatype.
0
StephenCairnsCommented:
Then you seem to be out of luck
http://msdn2.microsoft.com/en-us/library/ms182776.aspx

Timestamp canot be used in the way you want. It is a unique binary number not a date time at all
sorry
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
carmodykAuthor Commented:
Thank you all for helping.  I thought it best to supply everyone with some points.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.