Solved

timestamp

Posted on 2014-09-04
6
225 Views
Last Modified: 2014-09-04
1. I want to get all records from my table where timestamp = to today's date.

Timestamp column
2014-09-04 14:08:25.243

select *from tbl_sample  where sellerCode = 'abc' and convert(char(10), TimeStamp, 112) = convert(char(10), GETDATE(), 120)

2. I have a column call Postingdate - 2014-09-01 00:00:00.000. I also want it to returns where postingdate = today's date
0
Comment
Question by:VBdotnet2005
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 500 total points
ID: 40304806
select *from tbl_sample  where sellerCode = 'abc' and
(
   convert(char(10), TimeStamp, 112) = convert(char(10), GETDATE(), 112) OR
   convert(char(10), postingdate , 112) = convert(char(10), GETDATE(), 112)
)
0
 

Author Comment

by:VBdotnet2005
ID: 40304842
thank you :)
0
 

Author Comment

by:VBdotnet2005
ID: 40304845
Is this common?  convert(char(10), TimeStamp, 112) = convert(char(10), GETDATE(), 112)

select convert(char(10), '2014-09-01 00:00:00.000', 112)
result = 2014-09-01

select convert(char(10), GETDATE(), 112)
result = 20140904
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 40304849
http://www.blackwasp.co.uk/SQLDateTimeFormats.aspx

You can use 126 if you want to keep the dashes.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40304934
or refer to:
http://www.experts-exchange.com/Database/MS-SQL-Server/A_12315-SQL-Server-Date-Styles-formats-using-CONVERT.html

I would not recommend converting your timestamp column to varchar however, you should leave the data unconverted to take advantage of indexes. Please see this:

http://en.wikipedia.org/wiki/Sargable
0
 

Author Comment

by:VBdotnet2005
ID: 40305058
Thank you Portletpaul. Very useful.
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question