T-SQL: EOMONTH

Question

T-SQL: EOMONTH

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Gareth Davison Right there with Babe Points: 723 More actions · Answer 1

Alvin Ramard (9/19/2016)
BrainDonor (9/19/2016)
I couldn't see any of the answers that matched what I believed I should be getting - then I played around a little.
Where dates are concerned it is probably helpful to specify the date format in use, as this site has people from all over - and they might not be using the same date format as yourself.
That aside, a nice question.
With SQL Server, dates formatted as YYYY-XX-YY will always be YYYY-MM-DD,
YYYY-DD-MM is not a valid format with SQL Server.

This is true for the DATE, DATETIME2, and DATETIMEOFFSET types, but DATETIME, and SMALLDATETIME will interpret XXXX-XX-XX as YYYY-DD-MM if your date format is DMY. The following demonstrates this quite nicely, despite using the exact same literals, the two columns return two different values:

SET DATEFORMAT DMY;

DECLARE @DateTime DATETIME = '2012-02-01',

@Date DATE = '2012-02-01';

SELECT[Datetime] = EOMONTH( @Date, 2 ),

[Date] = EOMONTH( @DateTime, 2 );

For a dateformat of MDY, then both datatypes produce the same result:

SET DATEFORMAT MDY;

DECLARE @DateTime DATETIME = '2012-02-01',

@Date DATE = '2012-02-01';

SELECT[Datetime] = EOMONTH( @Date, 2 ),

[Date] = EOMONTH( @DateTime, 2 );

For DATETIME and SMALLDATETIME the only culture invariant format is YYYYMMDD

BrainDonor SSCoach Points: 19254 More actions · Answer 2

gareth.davison (9/20/2016)
Alvin Ramard (9/19/2016)
BrainDonor (9/19/2016)
I couldn't see any of the answers that matched what I believed I should be getting - then I played around a little.
Where dates are concerned it is probably helpful to specify the date format in use, as this site has people from all over - and they might not be using the same date format as yourself.
That aside, a nice question.
With SQL Server, dates formatted as YYYY-XX-YY will always be YYYY-MM-DD,
YYYY-DD-MM is not a valid format with SQL Server.
This is true for the DATE, DATETIME2, and DATETIMEOFFSET types, but DATETIME, and SMALLDATETIME will interpret XXXX-XX-XX as YYYY-DD-MM if your date format is DMY. The following demonstrates this quite nicely, despite using the exact same literals, the two columns return two different values:
SET DATEFORMAT DMY;
DECLARE @DateTime DATETIME = '2012-02-01',
@Date DATE = '2012-02-01';
SELECT[Datetime] = EOMONTH( @Date, 2 ),
[Date] = EOMONTH( @DateTime, 2 );
For a dateformat of MDY, then both datatypes produce the same result:
SET DATEFORMAT MDY;
DECLARE @DateTime DATETIME = '2012-02-01',
@Date DATE = '2012-02-01';
SELECT[Datetime] = EOMONTH( @Date, 2 ),
[Date] = EOMONTH( @DateTime, 2 );
For DATETIME and SMALLDATETIME the only culture invariant format is YYYYMMDD

Fascinating. Thank you for that.

Steve Hall
Linkedin
Blog Site

David Burrows SSC Guru Points: 65143 More actions · Answer 3

Date format default interpretation is dependant on the language specified for the login.

p.s. That is string interpretation.

Far away is close at hand in the images of elsewhere.
Anon.

DamianC SSCrazy Eights Points: 8023 More actions · Answer 4

DamianC

SSCrazy Eights

Points: 8023

November 2, 2016 at 10:08 am

#1910160

Like this function and its offset approach

- Damian