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 (http://blog.SQLAuthority.com)

About these ads

33 thoughts on “SQL SERVER – Introduction and Example for DATEFORMAT Command

  1. 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.

  2. 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

  3. 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

  4. 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

    • 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

  5. 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

  6. 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 …….

  7. 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

  8. 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

  9. 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……………

  10. 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..

  11. 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

  12. 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 !!!!

  13. 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..

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

  15. 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?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s