SQL SERVER – Introduction and Example for DATEFORMAT Command

While doing surprise code review of Jr. DBA I found interesting syntax DATEFORMAT. This keywords is very less used as CONVERT and CAST can do much more than this command. It is still interesting to learn about learn about this new syntax.

Sets the order of the dateparts (month/day/year) for entering datetime or smalldatetime data. This command allows you to input strings that would normally not be recognized by SQL server as dates. The SET DATEFORMAT command lets you specify order of data parts. The options for DATEFORMAT are mdy, dmy, ymd, ydm, myd, or dym. The default DATEFORMAT is mdy. This commands allows to INSERT/UPDATE dates with different formats without doing any special data conversions.The setting of SET DATEFORMAT is set at execute or run time and not at parse time. Following example demonstrate how character string can be converted to proper dateformat using SET DATEFORMAT command.

Example:

CREATE TABLE #tempTable (DateFormatSample SMALLDATETIME)
SET DATEFORMAT MDY
INSERT INTO #tempTable
VALUES ('09/28/2007')
SET DATEFORMAT YDM
INSERT INTO #tempTable
VALUES ('2007/28/09')
SET DATEFORMAT YMD
INSERT INTO #tempTable
VALUES ('2007/08/28')
SELECT DateFormatSample
FROM #tempTable
DROP TABLE #tempTable

Resultset:
DateFormatSample
———————–
2007-09-28 00:00:00
2007-09-28 00:00:00
2007-08-28 00:00:00

Reference : Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – FIX : Error 3154: The backup set holds a backup of a database other than the existing database
Next Post
SQL SERVER – Two Connections Related Global Variables Explained – @@CONNECTIONS and @@MAX_CONNECTIONS

Related Posts

33 Comments. Leave new

  • Hi,
    Great article
    If I put ” DMY ” then why it should not display as 28-09-2007 ?

    Reply
  • hi,

    I am getting problem with date when we are tring to insert into database using jdbc.we are getting error “optional
    feauture not implemented”
    please suggest me.

    Reply
  • dear Dave,

    i have problem displaying date format say 09/18/2008 using ms sql 2005 in java netbean ?

    i use
    static String selHoliday = “SELECT CONVERT(VARCHAR(10), Holiday, 101) AS [MM/DD/YYYY], Description from OFFDAY order by Holiday”;
    but still in java doesnot display the correct date format

    regards,
    Halim S

    Reply
    • You should never use Convert function in sql as you want tom show dates in java. Make use of format function there

      Reply
  • Hi Dave,

    could you please advise me on how to bulk insert data into sql table from a text file which has second column in date format ‘YYYY-MM-DD’. I am receiving error while trying to run bulkinsert command given below

    BULK INSERT dbo.RAMPUP_DAILYTXN_SEP08 FROM ‘C:\RAMPUP_TEST.txt’
    WITH (FIELDTERMINATOR = ‘|’)

    but receives error
    Msg 4864, Level 16, State 1, Line 1
    Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 2 (TRANSACTION_DATE)

    could you please advise on how to insert yyyy-mm-dd data from file to table in sql

    thanks

    nutty

    Reply
    • First make your column as varchar and import to a staging table from where you can convert them back to actual dates

      Reply
  • Hi.

    We have a problem that dateformat changed generally but we don’t know where is that setting (if it exists).

    So things that worked now produce out-of-range datetime situations.

    Desired: ymd
    Current: ydm
    Why=?

    Thanks
    Grega

    Reply
    • Run this to know the current date settings

      DBCC useroptions

      You can change the setting as you want

      But always make sure you input date in YYYYMMDD HH:MM:SS format in order to work with all date settings

      Reply
  • Hi,
    I am Rajesh. i have one column in a table and set the identity for that column.here how can i insert the value with out off the identity

    Reply
  • I am facing below error

    Error :

    Arithmetic overflow error converting expression to data type datetime.
    The statement has been terminated.

    Query :

    Update tbDocumentDetails set RoomNumber=’DELETED’, where ProcessFlag=’Y’ and datediff(day,CAST(CheckOutDate AS datetime),getdate())>= 7

    Please help me …….

    Reply
    • What is the datatype of CheckOutDate? Can you post what this returns?

      select max(CheckOutDate) from table

      Reply
  • Tried this way also for above problem.

    Update tbDocumentDetailsIndia set RoomNumber=’DELETED’ where ProcessFlag=’Y’ and CheckOutFlag=’Y’ and datediff(day,convert(datetime,CheckOutDate,103),getdate())>= 7

    Reply
  • Instead of using SET DATEFORMAT DMY in query level could we change it in server /DB level?

    Reply
    • As long as you use unambigious date format YYYYMMDD you don not need to worry about server settings
      See this for more informations

      Reply
      • Thanks, Dbcc useroptions command listing all server settings but can we change the dateformat from mdy to dmy?

      • You can temporarily change for your session using

        SET DATEFORMAT dmy

  • In SQL Server 2000, one of the date field is displaying date in mm/dd/yyyy format but I want to display the date in dd/mm/yyyy format.

    This I am trying from 1 month, Front end I am using VB 6 and backend sql server 2000.

    I am inserting data into the date field using format(fild name,”dd/mm/yyyy”) command but still in backend it is storing as mm/dd/yyyy.

    Kindly do the needful

    Reply
    • You dont need to worry abount formation as long as you use proper DATETIME datatype. Refer this for more informations

      Reply
  • pankaj aggarwal
    January 13, 2012 9:55 am

    i have a table emp,in emp table i have a column hiredate, i want to display all records but result should be in “dd/mmm/yyyy” format in hiredate column….
    how it can be possible,pls provide solution……………

    Reply
  • Ravinder Pal Singh
    January 18, 2012 4:25 pm

    Hi Pinal,

    I have a query regarding datetime inputs to SQL SERVER.

    How does SQL SERVER recognizes the inout date format. For example ’01-02-2012′. How does SQL SERVER recognizes whether its JAN 02 2012 OR FEB 01 2012.

    Please reply as it might help me fix issues in my project..

    Reply
  • hi,
    I have used this but it is not properly work if I Changed the format of date.
    How it is possible plz reply me.

    Reply
  • how to change date format in sql server 2005 after installation

    Reply
  • Hi All,

    Can I use this to validate a date field of format “DD-MM-YYYY” as given below. It is giving correnct result, But I want to know wheather this will work in every situation and after execution query is it require to reset the date format as I have written in the last line.

    SET DATEFORMAT DMY
    Update T set Error_desc = ‘ Posted date is Invalid.’
    from Temp_Data T
    where value_date is null OR ISDATE(value_date) = 0
    SET DATEFORMAT MDY

    Reply
  • No, value_date column is varchar and date store in a format of “DD-MM-YYYY”

    Reply
  • Deepak chaudhary
    June 2, 2012 12:30 pm

    i want to insert date in a table column which has data type datetime , using a stored procedure in this stored procedure variable datatype is date and want to give date in the format dd/mm/yyyy but its giving me error
    Error converting data type varchar to date.

    Please help me !!!!

    Reply
  • Mahesh Lalwani
    January 5, 2013 12:58 am

    Hi Pinal,
    I am facing below issue…
    Q1:
    INSERT INTO bankTransactionMaster
    (bankId, amount, transactionDate, particular, type, cashId, bank2TransactionId)
    VALUES (1, 3, ‘1-1-2013’, ‘a’, ‘cr’, NULL, NULL);

    The above query get executed successfully..

    Q2.
    INSERT INTO bankTransactionMaster
    (bankId, amount, transactionDate, particular, type, cashId, bank2TransactionId)
    VALUES (1, 3, ’15-1-2013′, ‘a’, ‘cr’, NULL, NULL);

    The above query gives me the error of Failed due to conversion of date/time.

    Q3:
    INSERT INTO bankTransactionMaster
    (bankId, amount, transactionDate, particular, type, cashId, bank2TransactionId)
    VALUES (1, 3, ‘1-21-2013’, ‘a’, ‘cr’, NULL, NULL);

    The above query get executed successfully..

    When I see data in my SQL SERVER through managment studio it displays me in dd-mm-yyyy format.

    So why should I have to execute the queries in MM/DD/YYYY format if its storing in DD/MM/YYYY format.. Due to this I am facing lot of trouble in my application..

    Reply
  • Intekhab uddin
    January 17, 2013 4:31 pm

    please give me solution that how create a date format (license point of view) in sql query(sql2008) like (‘stf4gMruT5gEB3axjC05Mg==’) as 31/01/2013.

    Reply
  • 1)set dateformat ydm
    insert into dummy values(‘2013-23-3’)

    2)set dateformat dmy
    insert into dummy values(‘2013-23-3’)

    In above 1) is correct but sql server returning both success. can you tell me why?

    Reply

Leave a Reply

Menu