Posts
27
Comments
57
Trackbacks
0
SET DATEFORMAT in SQL
Some times, you may want the database to interpret the date value as per the format you have provided rather than using the default database level format (which is mostly set to mdy as default US date format). SQL server SET DATEFORMAT syntax comes to rescue in this situations - 

you can set the dateformat to your convenience and then can run the insert or update with your format and SQL will not crib about it.  Please note, this is applicable only for the session and it does not set the date format at the server level. 
Also, the data in the database will get saved as per the format set for the server so there won't be any messing up of data.

A hypothetical scenario could be  - if you receive a CSV file which you need to import into database and all the dates in the CSV are in DD/MM/YYYY format whereas your database accepts date in YYYY/MM/DD format.

CREATE TABLE #temp (datesample smalldatetime)

 

SET DATEFORMAT MDY

INSERT INTO #temp 

VALUES ('09/28/2007')

SET DATEFORMAT YDM

INSERT INTO #temp 

VALUES ('2007/28/09')

SET DATEFORMAT YMD

INSERT INTO #temp 

VALUES ('2007/08/28')

SET DATEFORMAT DMY

INSERT INTO #temp 

VALUES ('28/12/2006')


SELECT DateSample

FROM #temp 

DROP TABLE #temp 



This would return the following output -

DateSample

2007-09-28 00:00:00

2007-09-28 00:00:00

2007-08-28 00:00:00

2006-12-28 00:00:00


Hope this helps someone.

Vipin

posted on Thursday, February 28, 2013 3:40 PM Print
Comments
No comments posted yet.

Post Comment

Title *
Name *
Email
Comment *