Solved

timestamp

Posted on 2014-09-04
6
224 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need help with a query 14 40
SQL Server maintenance plan not deleting backup files as anticipated 4 19
Database-Scoped Permissions 2 19
Database Mail Profiles 1 25
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

696 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