You must Sign In to post a response.
  • Category: SQL Server

    How to change Date Format in sql server 2008 r2

    hi All ,

    In my sql server the date format is like ss-mm-yyyy.
    if i insert like

    insert into dates values('11/10/2015','12/10/2015') => this query
    it gives output like 11-10-2015.
    but i want output how am inserting. my inserted format.
    it should be 11/10/2015.
    So how to i change the default format.

    i want the date format will be the dd/mm/yyyy format Always.
    so how can i set it in sqlserver.(bankend)

    Thanks with
    Paul.S
  • #765873
    very common requirement of developers is to format datetime to their specific need. Every geographic location has different need of the date formats. Some countries follow the standard of mm/dd/yy and some countries as dd/mm/yy. The need of developer changes as geographic location changes.
    You could use SET DATEFORMAT statement in SQL
    check below snippet
    set the format and then INSERT

    declare @dates table (orig varchar(50) ,parsed datetime)

    SET DATEFORMAT ydm;

    insert into @dates
    select '2008-09-01','2008-09-01'

    SET DATEFORMAT ymd;
    insert into @dates
    select '2008-09-01','2008-09-01'

    select * from @dates

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #765881
    Hi,

    SELECT TOP 1 empid,empname,

    CONVERT(varchar,joindate,103) 'JoinDate'
    FROM Emptest


    hope this will help.

  • #765888
    thanks Sr.Sriram. your code is working fine .

    But my requirement is if mi am insert a date in my sql table format like 12/05/2016 . it will inserted and give my inserted date output is like 2016-05-12 . it is set default format is like
    yyyy-mm-dd in my sql. So how set it to my our given format. that is in need.

    for example
    if i insert date on my sql table using sql query.
    1.date ='12/05/2016' my needed output : 12/05/2016
    2.date ='12-12-2016 my needed output:12-05-2016
    3.date='12:05:2016 my needed output :12:05:2016

    but now if i am inserted any format of above it give output only like 2016-05-12

    So help me to how i am get output to my given format

    Thanking you
    Paul.S

  • #765890
    Change your dateTime format of Server your SQL date time will be change as per the requirement. basically SQL collect system datetime format and show it like that.
    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #765891
    Hi,

    SQL Server Date is a binary field (stores binary value regardless of formatting).
    So you do not store date in SQL by any format - only displays it in your desired format in your application.


    CREATE TABLE #MY_TABLE (TYPE VARCHAR(5),START_DATE DATETIME)

    SET DATEFORMAT DMY --Note this setting

    INSERT INTO #MY_TABLE (TYPE,START_DATE)
    VALUES('DBLMS','01.03.2011');

    SELECT TYPE, CONVERT(VARCHAR(10), START_DATE, 105) AS [DD-MM-YYYY]
    FROM #MY_TABLE


    101 - DD/MM/YYYY
    104 - DD.MM.YYYY
    105 - DD-MM-YYYY


    Hope This will help

  • #766516
    Hi,

    Kindly try with the following Query for your Question. It may solve your Problems. while Using dates for inserting into specific format means we have to convert into our needed format Like dd/mm/yyyy or mm-dd-yyyy.

    1. Insert into Table_Name(Date_column,Date_Column1)
    Values (convert(varchar(12),'11/12/2012',103),convert(varchar(12),'12/12/2016',103))
    Or
    2. Insert into Table_Name(Date_Column,Date_Column1)
    Select convert(varchar(12),'11/12/2012',103),convert(varchar(12),'12/12/2016',103)
    union
    convert(varchar(12),'11/12/2012',103),convert(varchar(12),'12/12/2016',103)


    Regards,
    Karunanidhi.K


Sign In to post your comments