VBdotnet2005
asked on
timestamp
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
select convert(char(10), '2014-09-01 00:00:00.000', 112)
result = 2014-09-01
select convert(char(10), GETDATE(), 112)
result = 20140904
or refer to:
https://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
https://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
ASKER
Thank you Portletpaul. Very useful.
ASKER