Using SET DATEFORMAT with MS SQL Server
I just wrote a stored procedure for Microsoft SQL Server which creates a report for the previous month. However, for certain months MS SQL Server was assuming the date was formatted as yyyy-dd-mm instead of yyyy-mm-dd. In turns out that you can set the dateformat and avoid these issues by using:
SET DATEFORMAT ymd;
Here's a quick test I wrote:
DECLARE @startdate as datetime
DECLARE @enddate as datetime
DECLARE @month as int
SET DATEFORMAT ydm;
SET @month = Month(GetDate())-1
SET @startdate = CAST('2010-' + CAST(@month as nvarchar(2)) + '-01 00:00:00.000' AS datetime)
print @startdate
-- switch date format
SET DATEFORMAT ymd;
SET @month = Month(GetDate())-1
SET @startdate = CAST('2010-' + CAST(@month as nvarchar(2)) + '-01 00:00:00.000' AS datetime)
print @startdate
The output is:
Jan 11 2010 12:00AM
Nov 1 2010 12:00AM
- Posted in:
- SQL
