Solved

timestamp

Posted on 2014-09-04
6
206 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
  • 3
  • 2
6 Comments
 
LVL 39

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 39

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 48

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

746 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now