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)

List of SQL Server Functions like Cast(), Round() Ceiling() Floor()

Sql server has many inbuild function like sum(), count(),round(), cast() .....
examples of some are

select ((30.250250/80)*100) as Percentage result: 37.812812500

select round(((30.250250/80)*100),1) result: 37.800000000
select round(((30.250250/80)*100),2) result: 37.810000000
select round(((30.250250/80)*100),3) result: 37.812000000
select round(((30.250250/80)*100),4) result: 37.812800000
select round(((30.250250/80)*100),5) result: 37.812810000

select ROUND(748.58, -1) result: 750.00
select ROUND(748.58, -2) result: 700.00
select ROUND(748.58, 0) result: 749.00
select ROUND(748.58, 1) result: 748.60
select ROUND(748.58, 2) result: 748.58


SELECT ROUND(123.4567, -1); result: 120.0000
SELECT ROUND(123.4567, -2); result: 100.0000
SELECT ROUND(123.4567, -3); result: 0.0000
SELECT ROUND(123.4567, -4); result: 0.0000
SELECT ROUND(123.4567, -5); result: 0.0000


select Ceiling(((30.250250/80)*100)) result: 38
select Floor(((30.250250/80)*100)) result: 37

select CAST(((30.250250/80)*100) AS DECIMAL (9,0)) result: 38
select CAST(((30.250250/80)*100) AS DECIMAL (9,1)) result: 37.8
select CAST(((30.250250/80)*100) AS DECIMAL (9,2)) result: 37.81
select CAST(((30.250250/80)*100) AS DECIMAL (9,3)) result: 37.8121
select CAST(((30.250250/80)*100) AS DECIMAL (9,4)) result: 37.8128
select CAST(((30.250250/80)*100) AS DECIMAL (9,5)) result: 37.81281

Note: In this case DECIMAL (9,0) : 9 represent no of digit before decimal point
and 0,1,2,3,4... represent digit after decimal point

Restriction in Group By and Having clause oF Select Statement in sql server

In case of Reporting application of employee / Payroll system we have to calculate the no of hours worked by each employee in different department on different tasks.

To generate a report based on this data, we have to use aggregate function like sum()
in SQL server.
But for that we have to use �Group by� clause to make group of employee by (Employee_Id) and if required we also have to use �Having clause� with group by clause in select statement to restrict some group of record to display.

While using Group By clause there is restriction of using it.

The columns which are used in SELECT clause should appear in GROUP BY clause.
Except the column used in aggregate function.
That is all the column/field in select statement should be used in group by clause and it is must. But some of the fields like no_Of_Hours_worked, Sum_of_days etc should use aggregate function and it is not mandatory to put it in Group clause.


Example

SELECT Employee_ID,Department_Name,
Taskname,sum([HoursWorked])as Workered_hrs ,no_of_hrs as Std_hours
FROM [DailyUpdate] upd ,[Eployee_Master] rep, [Task_Master] task
WHERE upd.Report_ID= rep.ID and upd.Task_ID = task.TaskId
group by Department_Name,Taskname,Employee_ID,no_of_hrs
having Employee_ID = 16


Finally:

The columns which are used in SELECT clause should appear in GROUP BY clause except the column used in aggregate function.

Remove time from datetime in SQL Server

when we retrive date from sql server it gives aoutput in Date-time format like

select getdate(): 2009-11-02 11:58:09.513

It consist of today's date, time in hrs:min:sec:msec

But Many times requirement is to display only day not time. example Daily reports

For this there is a function like:

select convert(char,getdate(),100): Nov 2 2009 12:01PM

select convert(char,getdate(),101): 11/02/2009

select convert(char,getdate(),102): 2009.11.02

select convert(char,getdate(),103): 02/11/2009

select convert(char,getdate(),104): 02.11.2009

select convert(char,getdate(),105): 02-11-2009

select convert(char,getdate(),106): 02 Nov 2009

select convert(char,getdate(),107): Nov 02, 2009

select convert(char,getdate(),108): 12:05:19

select convert(char,getdate(),109) : Nov 2 2009 12:05:19:733PM

select convert(char,getdate(),110) : 11-02-2009

select convert(char,getdate(),111): 2009/11/02

select convert(char,getdate(),112) : 20091102

select convert(char,getdate(),113) : 02 Nov 2009 12:16:44:343

select convert(char,getdate(),114): 12:16:44:343