[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 908
  • Last Modified:

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.
0
carmodyk
Asked:
carmodyk
  • 4
  • 3
  • 2
  • +1
4 Solutions
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
carmodykAuthor Commented:
Thank you all for helping.  I thought it best to supply everyone with some points.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now