How to handle date in sql server 2008

By
When we insert a date in a table,by default SQL Server accepts date in 'mdy' (Month-date -year) format. If we want to change 'mdy' Format to 'dmy' or other format then we can easily change .Which is as shown below:

There are 3! combination of date format in sql server.It means we can insert values in 6 format using sql command in sql server.Which are given below

   1. ) Date format 'dmy'                                                                                                       
          set dateformat 'dmy'
      update studentdetails set regdate='15/01/2011'
      where sid=103
output:-

   2. ) Date format 'ymd'                                                                                                       
       set dateformat 'ymd'
       update studentdetails set regdate='2013/05/07'
       where sid=102
Output:-

   3. ) Date format 'ydm'                                                                                                       
        set dateformat 'ydm'
        update studentdetails set regdate='2013/05/17'
        where sid=101
Output:- It is problem in sql server ,it does not take value 'ydm' format'.You can check in your sql server 2008.

  4. ) Date format 'dym'                                                                                                       
     set dateformat 'dym'
     update studentdetails set regdate='15/2005/07'
     where sid=103
output:-

 5. ) Date format 'myd'                                                                                                       
      set dateformat 'myd'
      update studentdetails set regdate='05/2005/17'
      where sid=104
Output:-

 6. ) Date format 'mdy' (default format )                                                                         
      set dateformat 'mdy'
      update studentdetails set regdate='05/24/2002'
      where sid=102
Output:-

Cast and Convert function:-                                                                 
 These two function can be used to convert a particular type value to another type.I have created a studentdetails table for implementing the cast and convert function. which is shown below:-
select *from studentdetails
Output:-

1.) Cast function:-
syntax:-
Cast (<column> as <data type>)

select sid as 'students id',sname as 'name',cast(regdate as varchar(30))as 'joining date' from studentdetails

Output:-

2. ) Convert function:-
Syntax:-
Convert (<data type><column><format>)

select sid as 'student_id',sname as 'Name',convert(varchar(30),regdate,104)as 'date' from studentdetails
Output:-

Difference between Cast and Convert Function:-
  • Cast is ANSI SQL-92 but Convert is a specific to SQL Server.
  • Except 'date' data type ,if we convert any type value into any other type ,there is no difference between Cast and Convert function.
  • If we want to convert date data type value to varchar' data type the convert function will be used.Because date format is exist in Convert function not Cast function.
group by clause:-                                                                                                                                    
When we run aggregated function in select query,we get the output on the basis of all the records but if we want to use aggregated function,then we should give us output on the basis of group and column and we can used 'group by clause' before  grouping the records.If we want to keep some conditions,we can specify 'where' clause and after grouping the records if we want to some conditions then we can use 'having' clause.
There are some commands to explain the group by clause. which are given below:
1. ) First create a students table as shown below:



2. )  Use group by command as given below:
 
   select deptName,count(*) from students where salary>1000
   group by deptName
Output:-

3. ) Use group by and Having clause as given below:-

     select deptName,count(*) from students 
    group by deptName
    having COUNT(*)>1
 Output:-

4. ) Use group by , where clause and having clause as given below:-

      select deptName,count(*)as Total from students where salary >=300
      group by deptName
      having COUNT(*)>1
Output:-

For More:- 
  1.  Ms Sql server
  2. Stored Procedure
  3. Setup File
  4. Add Identity Column
  5. Make Captcha image for Asp.Net Application
I hope this helpful for you.

1 comment:

  1. thank u for ur tutorials.........its help me a
    lot

    ReplyDelete

Powered by Blogger.