SQL Date Functions

SQL Date Functions
1.To get current date and time.
select getdate() as TodayDate
TodayDate
2011-11-05 06:55:22.293

2.To convert date into different formats
Syntax for converting
select convert (Data type in which to convert, column name, format)
select convert(varchar,getdate(),104)as Converted_Date
Converted_Date
05.11.2011

There are different formats in which you can change your date.
100 mon dd yyyy hh:miAM (or PM)
101 mm/dd/yy
102 yy.mm.dd
103 dd/mm/yy
104 dd.mm.yy
105 dd-mm-yy
106 dd mon yy
107 Mon dd, yy
108 hh:mm:ss
109 mon dd yyyy hh:mi:ss:mmmAM (or PM)
110 mm-dd-yy
111 yy/mm/dd
112 yymmdd
113  dd mon yyyy hh:mm:ss:mmm(24h)
114 hh:mi:ss:mmm(24h)
120 yyyy-mm-dd hh:mi:ss(24h)
121 yyyy-mm-dd hh:mi:ss.mmm(24h)
126 yyyy-mm-ddThh:mm:ss.mmm(no spaces)
130 dd mon yyyy hh:mi:ss:mmmAM
131
DateAdd function:
3To add days, months and years into a column value.
Syntax for adding
select dateadd(what to add,amount to add,in which column to add)
select getdate()as Simple_Date,dateadd(day,10,getdate())as Date_Added
Adds 10 days to simple date’s day.
Simple_Date
Date_Added
2011-11-05 07:21:34.357
2011-11-15 07:21:34.357

You can also add months and years like this.
select getdate()as Simple_Date,dateadd(month,5,getdate())as Date_Added

Adds 5 months to Simple Date’s month.
Simple_Date
Date_Added
2011-11-05 07:32:52.560
2012-04-05 07:32:52.560


select getdate()as Simple_Date,dateadd(year,2,getdate())as Date_Added
Adds two years to Simple Date’s year.

Simple_Date
Date_Added
2011-11-05 07:35:26.827
2013-11-05 07:35:26.827

DateDiff Function:
Datediff function is used to subtract one date value from another in terms of days,months and years.
Syntax for subtracting
select datediff(Which thing to subtract,column to be subtracted,from which column to subtract)
Here we have a table on which we will apply datediff functions
appointment_no
app_date
app_status
A000     
2010-10-25 00:00:00.000
Ok       
A001     
2011-06-10 00:00:00.000
Cancel   
A002     
2009-01-30 00:00:00.000
Ok       
A003     
2010-04-10 00:00:00.000
Ok       

To subtract days.
select app_date , getdate()as Present_date,
datediff (day,app_date,getdate())
as changd_day from appointment

Calculates difference in days from Present_Date to the date specified in app_date column

app_date
Present_date
changed_day
2010-10-25 00:00:00.000
2011-11-06 01:01:24.000
377
2011-06-10 00:00:00.000
2011-11-06 01:01:24.000
149
2009-01-30 00:00:00.000
2011-11-06 01:01:24.000
1010
2010-04-10 00:00:00.000
2011-11-06 01:01:24.000
575


To subtract months.
select app_date , getdate()as Present_date,
datediff (month,app_date,getdate())
as changed_month from appointment

Calculates differnce in months from Present_Date to the date specified in app_date column


app_date
Present_date
changed_month
2010-10-25 00:00:00.000
2011-11-06 01:04:32.030
13
2011-06-10 00:00:00.000
2011-11-06 01:04:32.030
5
2009-01-30 00:00:00.000
2011-11-06 01:04:32.030
34
2010-04-10 00:00:00.000
2011-11-06 01:04:32.030
19

To subtract years.
select app_date , getdate()as Present_date,
datediff (year,app_date,getdate())
as changed_years from appointment
Calculates differnce in years from present date to the date specified in app_date column

app_date
Present_date
changed_years
2010-10-25 00:00:00.000
2011-11-06 01:09:52.687
1
2011-06-10 00:00:00.000
2011-11-06 01:09:52.687
0
2009-01-30 00:00:00.000
2011-11-06 01:09:52.687
2
2010-04-10 00:00:00.000
2011-11-06 01:09:52.687
1

DatePart Function:
Datepart function returns the part of the date specified as parameter from the column specified.
Syntax for datepart function
SELECT DATEPART(part to be returned(e.g year,month,day),column name)

SELECT getdate()as Present_date,DATEPART(dd,getdate()) AS Day

Returns the day from Present_Date

Present_date
Day
2011-11-06 19:29:30.450
6

SELECT getdate()as Present_date,DATEPART(mm,getdate()) AS Month

Returns the month from Present_Date

Present_date
Day
2011-11-06 19:29:30.450
11

SELECT getdate()as Present_date,DATEPART(yy,getdate()) AS Year


Returns the year from Present_Date


Present_date
Day
2011-11-06 19:29:30.450
2011

Now we combine all the datepart functions to see what actually is happening

SELECT getdate() as Present_Date,DATEPART(yyyy,getdate()) AS Year,
DATEPART(mm,getdate()) AS Month,
DATEPART(dd,getdate()) AS Day

Present_date
Year
Month
Day
2011-11-06 19:41:47.090
2011
11
6


Day Fuction:
Returns the day of the month based on the month field as an integer value in the range of 1-31
select getdate()as Present_Date,day(getdate())as Day
Present_Date
Day
2011-11-06 21:11:37.763
6

Month Fuction:
Returns the month based on the month field as an integer value in the range of 1-12
select getdate()as Present_Date,month(getdate())as Month
Present_Date
Month
2011-11-06 21:11:37.763
11

Year Fuction:
Returns the year based on year field as an integer value.
select getdate()as Present_Date,year(getdate())as year
Present_Date
Year
2011-11-06 21:11:37.763
2011






Comments

Post a Comment

Popular posts from this blog

Using Progress Bar In C#

Image
Using Progress Bar in C#: Progress Bar is very commonly used control in many applications to display the progress of task being performed. Progress Bar is displayed when copying files, deleting files or installing some software. How to use Progress Bar: ·          Start New Windows Form Application. ·          Go to Toolbox. ·          Select Progress Bar and Drag it to your Form. To make Progress Bar working you need to know some properties of Progress Bar Progress Bar.Maximum: Defines how long it should run Progress Bar.Step: Defines how much it should increase current position of Progress Bar. Progress Bar.Style: How should it look like? There is also a very basic method which is used to run Progress Bar properly. Progress Bar.PerformStep() Let’s see an example. In this example we will copy files and folders from one location to another and show the progress bar wh...
Read more

Get elements by class name in javascript

To get elements using class name we use getElementsByClassName method available with document object. This method takes name of class an argument.  It returns array of elements because more than one element(s) can have same class. < input type ="button" value ="Get By Class" onclick =" GetByClassName() " /> < div id ="myDiv" class ="myClass">     This is a div </ div > < script type ="text/javascript">   function GetByClassName() {         var elements = document.getElementsByClassName( "myClass" );         if (elements.length > 0) {             alert(elements[0].innerHTML);         }     else       alert( "No matching element found" );    }   </ script >
Read more

Jquery serer side datatables in asp.net

In this post we will learn how we can implement jquery   server side datatables in asp.net mvc application. We need to add datatables library to our page, we can add references of libray from datatables <a href="https://datatables.net/" target="_blank">website</a>. We need to create an html table on which we will apply datatable. <table class="table table-striped table-bordered table-hover table-checkable order-column dataTable" id="tblInventoryItems">   <thead>     <tr>       <th>Item Name</th>       <th>Item Price</th>       <th>Item Quantity</th>       <th></th>     </tr>   </thead>   <tbody></tbody> </table> Then we need the code which will apply the datatable to above html table. Below is the code to apply datatable $("yourtableid").DataTable({         "p...
Read more