Get all record entered today or yesteday in sql server, Other Sql Functions of Conversions

Most Of the time we fiddle in Sql for performing the operations on Date format.
any other Data format is easy to manipulate like numeric, int etc on which we can perform operation
like (<,>,=,<=,,,,,)

But What when we have a requirement that show all the record which are entered on current date , on specified date, before a current date
etc.

take a example i want all record on today's date

We cannot use :
select * from dailyupdates where Date = getdate()

this will result in no record found....

We cannot perform any operation like : "like,in" etc...

This is because the date is in datetime format andwhere clause cjheck time as well as date


So we have to use convert funtions in Sql server.
By using convert function we convert the date in Varchar format and then substring(cut) the date format only
and then check the Date format

So result is :
This solution is to get all the record entered today
select * from Dailyreports where convert(varchar(10),Date, 101) = convert(varchar(10),getdate(), 101)


The solution for getting Yesterday's entered record are
select * from Dailyreports where convert(varchar(10),Date, 101) = convert(varchar(10),getdate()-1, 101)

Likely other functions are

-- to get today's Date
select getdate() -- 2009-11-27 16:58:40.413 (yyyy-mm-dd)

--To get Current month
select convert(varchar(2),getdate(), 101) --the result is 11 : month

--To get Current month and Date
select convert(varchar(5),getdate(), 101) --the result is 11/27 27th day of 11th Month

-- to get date in regular format
select convert(varchar(10),getdate(), 101) --the result is 11/27/2009 : Current date mm/dd/yyyy


---------


-- to get date in .(dot) format
select convert(varchar(10),getdate(), 102) --the result is 2009.11.27 (yyyy.mm.dd)

-- the regular way to show Dates
select convert(varchar(10),getdate(), 103) --the result is 27/11/2009 (dd/mm/yyyy)

-- Date in Dot(.) format
-- can use substring functions to gate dd mm and yyyy
select convert(varchar(10),getdate(), 104) --the result is 27.11.2009 (dd.mm.yyyy)

-- Date in Dash(-) format
select convert(varchar(10),getdate(), 105) --the result is 27-11-2009 (dd-mm-yyyy)

-- Abbrivated way of expressing Date
select convert(varchar(11),getdate(), 106) --the result is 27 Nov 2009 (dd month yyyy)

--New Abbr way of expressing Date
select convert(varchar(12),getdate(), 107) --the result is Nov 27, 2009 (month dd, yyyy)


-- get time ---
select convert(varchar(8),getdate(), 108) --the result is 17:05:17 hrs:min:sec

-- abbr way of display date
select convert(varchar(12),getdate(), 109) --the result is Nov 27 2009 (month dd yyyy)

-- way of displaying date
select convert(varchar(15),getdate(), 110) --the result is 11-27-2009 (mm-dd-yyyy)

-- way of displaying date
select convert(varchar(12),getdate(), 111) --the result is 2009/11/27 (yyyy/mm/dd)

-- way of displaying date 'Unique way'
select convert(varchar(12),getdate(), 112) --the result is 20091127

---- way of displaying date
select convert(varchar(25),getdate(), 113) --the result is 27 Nov 2009 17:08:53:500

-- get date hrs:min:sec:msec
select convert(varchar(14),getdate(), 114) --the result is 17:09:21:670
-------------------------

-- to get today's date
select getdate() --the result is 2009-11-27 17:16:41.093

-- to get Yesterday's date
select getdate()- 1 -- the result is 2009-11-26 17:16:49.233

-- to get all records irresrective of time (only check date)
select convert(varchar(10),getdate()-1, 101) -- the result is 11/26/2009

-- to get all reords of employee work from dailyreports of yesterday
select * from Dailyreports where convert(varchar(10),Date, 101) = convert(varchar(10),getdate()-1, 101)