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)
33 Comments. Leave new
Hi,
Great article
If I put ” DMY ” then why it should not display as 28-09-2007 ?
Note that it would treat the incoing dates as that format
Diplaying date is a different case
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.
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
You should never use Convert function in sql as you want tom show dates in java. Make use of format function there
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
First make your column as varchar and import to a staging table from where you can convert them back to actual dates
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
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
Insert into table default values
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 …….
What is the datatype of CheckOutDate? Can you post what this returns?
select max(CheckOutDate) from table
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
Instead of using SET DATEFORMAT DMY in query level could we change it in server /DB level?
As long as you use unambigious date format YYYYMMDD you don not need to worry about server settings
See this for more informations
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
You dont need to worry abount formation as long as you use proper DATETIME datatype. Refer this for more informations
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……………
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..
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.
how to change date format in sql server 2005 after installation
This is useless. Why do you want to change the setting? Read these posts
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
It will work fine as long as there are valid dates. Is the column a datetime?
No, value_date column is varchar and date store in a format of “DD-MM-YYYY”
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 !!!!
Read this post fully
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..
please give me solution that how create a date format (license point of view) in sql query(sql2008) like (‘stf4gMruT5gEB3axjC05Mg==’) as 31/01/2013.
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?