SQL SERVER – Introduction and Example for DATEFORMAT Command « Journey to SQL Authority with Pinal Dave

SQL SERVER – Introduction and Example for DATEFORMAT Command

September 28, 2007 by pinaldave

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)

Posted in Pinal Dave, SQL, SQL Authority, SQL DateTime, SQL Documentation, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | 5 Comments

5 Responses

on October 4, 2007 at 9:07 pm | Reply Aruna Namal

Hi,
Great article
If I put ” DMY ” then why it should not display as 28-09-2007 ?
on November 2, 2007 at 3:45 pm | Reply prudhvi

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.
on September 18, 2008 at 3:38 pm | Reply halim

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
on October 7, 2008 at 7:01 pm | Reply AP

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
on October 24, 2008 at 1:40 pm | Reply GregaJ

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