It's the end of the year

• Alvin Ramard

  SSC-Forever

  Points: 41190

  More actions

  January 4, 2016 at 3:59 pm

  #1849404

  Eirikur Eiriksson (1/4/2016)

  ---

  Alvin Ramard (1/4/2016)

  ---

  Eirikur Eiriksson (1/4/2016)

  ---

  Jeff Moden (1/4/2016)

  ---

  Lynn Pettis (1/4/2016)

  ---

  Good question. Just one minor thing, I'd use the following the end of the calendar year:

  SELECT DATEADD(DAY, -1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0));

  With the understanding that both -1's (1899-12-31) are in a year earlier than 0 (1900-01-01), you can get way with murder.

  SELECT DATEADD(yy,DATEDIFF(yy,-1,GETDATE()),-1); --Last Day of Current Year

  Never imagined I would ever boost in this way but mine is (4 characters) shorter than yours:-D

  😎

  Edit: parenthesis

  You got me beat too.

  SELECT CAST(CAST(YEAR(GETDATE()) AS CHAR(4)) + '1231' AS DATE)

  Shorter version of yours, same length as mine:Whistling:

  😎

  SELECT EOMONTH(STR(YEAR(GETDATE()))+'1231')

  Last one doesn't work on my server (SQL2008R2). I get the following error: 'EOMONTH' is not a recognized built-in function name.

  ---

  
  Alvin Ramard
  Memphis PASS Chapter[/url]

  All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

  For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

• Eirikur Eiriksson

  SSC Guru

  Points: 182917

  More actions

  January 4, 2016 at 4:05 pm

  #1849405

  Alvin Ramard (1/4/2016)

  ---

  Eirikur Eiriksson (1/4/2016)

  ---

  Alvin Ramard (1/4/2016)

  ---

  Eirikur Eiriksson (1/4/2016)

  ---

  Jeff Moden (1/4/2016)

  ---

  Lynn Pettis (1/4/2016)

  ---

  Good question. Just one minor thing, I'd use the following the end of the calendar year:

  SELECT DATEADD(DAY, -1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0));

  With the understanding that both -1's (1899-12-31) are in a year earlier than 0 (1900-01-01), you can get way with murder.

  SELECT DATEADD(yy,DATEDIFF(yy,-1,GETDATE()),-1); --Last Day of Current Year

  Never imagined I would ever boost in this way but mine is (4 characters) shorter than yours:-D

  😎

  Edit: parenthesis

  You got me beat too.

  SELECT CAST(CAST(YEAR(GETDATE()) AS CHAR(4)) + '1231' AS DATE)

  Shorter version of yours, same length as mine:Whistling:

  😎

  SELECT EOMONTH(STR(YEAR(GETDATE()))+'1231')

  Last one doesn't work on my server (SQL2008R2). I get the following error: 'EOMONTH' is not a recognized built-in function name.

  EOMONTH is 2012=+

  😎

  You can still chop it close

  SELECT CAST(STR(YEAR(GETDATE()))+'1231'AS DATE)

• Eirikur Eiriksson

  SSC Guru

  Points: 182917

  More actions

  January 4, 2016 at 4:31 pm

  #1849409

  For fun and completion, comparison of the previously posted "short" methods

  😎

  Test harness

  USE tempdb;

  GO

  SET NOCOUNT ON;

  DECLARE @SAMPLE_SIZE INT = 2000000;

  --/*

  IF OBJECT_ID(N'dbo.TBL_METHOD_TEST' ) IS NOT NULL DROP TABLE dbo.TBL_METHOD_TEST ;

  CREATE TABLE dbo.TBL_METHOD_TEST

  (

  MT_ID INT NOT NULL CONSTRAINT PK_DBO_METHOD_TEST_MT_ID PRIMARY KEY CLUSTERED

  );

  CREATE UNIQUE NONCLUSTERED INDEX UNQNCLIDX_DBO_METHOD_TEST_MT_ID ON dbo.TBL_METHOD_TEST (MT_ID ASC);

  INSERT INTO dbo.TBL_METHOD_TEST(MT_ID)

  SELECT TOP(@SAMPLE_SIZE)

  ROW_NUMBER() OVER

  (

  ORDER BY (SELECT NULL)

  )

  FROM sys.all_columns SAC1

  CROSS JOIN sys.all_columns SAC2

  CROSS JOIN sys.all_columns SAC3

  CROSS JOIN sys.all_columns SAC4

  ;

  -- */

  ---- TEST EXECUTION ----

  DECLARE @timer TABLE (T_TEXT VARCHAR(100) NOT NULL, T_TS DATETIME2(7) NOT NULL DEFAULT (SYSDATETIME()));

  DECLARE @INT_BUCKET_01 INT = 0;

  DECLARE @DATETIME_BUCKET_01 DATETIME = CONVERT(DATETIME,GETDATE(),0);

  DECLARE @DATE_BUCKET_01 DATE = CONVERT(DATE,GETDATE(),0);

  INSERT INTO @timer(T_TEXT) VALUES ('DRY RUN 1');

  SELECT

  @INT_BUCKET_01 = M.MT_ID

  FROM dbo.TBL_METHOD_TEST M;

  INSERT INTO @timer(T_TEXT) VALUES ('DRY RUN 1');

  INSERT INTO @timer(T_TEXT) VALUES ('DATEFROMPARTS 1');

  SELECT

  @DATETIME_BUCKET_01 = DATEFROMPARTS(YEAR(GETDATE()),12,31)

  FROM dbo.TBL_METHOD_TEST M;

  INSERT INTO @timer(T_TEXT) VALUES ('DATEFROMPARTS 1');

  INSERT INTO @timer(T_TEXT) VALUES ('EOMONTH 1');

  SELECT

  @DATETIME_BUCKET_01 = EOMONTH(STR(YEAR(GETDATE()))+'1231')

  FROM dbo.TBL_METHOD_TEST M;

  INSERT INTO @timer(T_TEXT) VALUES ('EOMONTH 1');

  INSERT INTO @timer(T_TEXT) VALUES ('CAST 1');

  SELECT

  @DATETIME_BUCKET_01 = CAST(STR(YEAR(GETDATE()))+'1231'AS DATE)

  FROM dbo.TBL_METHOD_TEST M;

  INSERT INTO @timer(T_TEXT) VALUES ('CAST 1');

  INSERT INTO @timer(T_TEXT) VALUES ('DATEADD(DAY)');

  SELECT

  @DATETIME_BUCKET_01 = DATEADD(DAY, -1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0))

  FROM dbo.TBL_METHOD_TEST M;

  INSERT INTO @timer(T_TEXT) VALUES ('DATEADD(DAY)');

  INSERT INTO @timer(T_TEXT) VALUES ('DATEADD(yy)');

  SELECT

  @DATETIME_BUCKET_01 = DATEADD(yy,DATEDIFF(yy,-1,GETDATE()),-1)

  FROM dbo.TBL_METHOD_TEST M;

  INSERT INTO @timer(T_TEXT) VALUES ('DATEADD(yy)');

  SELECT

  T.T_TEXT

  ,DATEDIFF(MICROSECOND,MIN(T.T_TS),MAX(T.T_TS)) AS DURATION

  ,DATEDIFF(MICROSECOND,MIN(T.T_TS),MAX(T.T_TS)) / (@SAMPLE_SIZE + 0.0) AS PER_ROW_COST

  FROM @timer T

  GROUP BY T.T_TEXT

  ORDER BY DURATION ASC;

  Results (old i5 laptop)

  T_TEXT DURATION PER_ROW_COST

  ------------------- ----------- ----------------

  DATEFROMPARTS 1 206011 0.1030055000000

  CAST 1 226013 0.1130065000000

  EOMONTH 1 231014 0.1155070000000

  DATEADD(yy) 236013 0.1180065000000

  DATEADD(DAY) 249014 0.1245070000000

  DRY RUN 1 374022 0.1870110000000

• Rich Mechaber

  SSChampion

  Points: 11130

  More actions

  January 6, 2016 at 10:56 am

  #1849954

  <snip>

  Hugo Kornelis (12/31/2015)

  ---

  [Final note: yyyy-mm-dd is also an official ISO standard and supported for the newer data types, but the old data types for legacy reasons still support other formats that look the same, so implicit conversion based on yyyy-mm-dd is not 100% safe]

  </snip>

  Thank you for the longer explanation, Hugo (and a belated Happy New Year).

  As follow-up to Hugo's last point (quoted above), here's a page exploring the SQL language setting dependency for DATETIME data types.

  One noticeable gotcha from half-way down that page: 'YYYY-MM-DD' for a DATETIME is language dependent in SQL Server. This, for example, throws an error:

  SET LANGUAGE british

  SELECT CAST('2003-02-28' AS datetime);

  Server: Msg 242, Level 16, State 3, Line 4

  The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

  Rich

• Toreador

  SSChampion

  Points: 11368

  More actions

  January 7, 2016 at 1:55 am

  #1850120

  Rich Mechaber (1/6/2016)

  ---

  This, for example, throws an error:

  SET LANGUAGE british

  SELECT CAST('2003-02-28' AS datetime);

  Server: Msg 242, Level 16, State 3, Line 4

  The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

  I wonder why that is? Our date format is dd mm yyyy, or yyyy-mm-dd, but never yyyy-dd-mm (at least I've never seen a date expressed in that form)

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  January 7, 2016 at 8:17 am

  #1850202

  Toreador (1/7/2016)

  ---

  Rich Mechaber (1/6/2016)

  ---

  This, for example, throws an error:

  SET LANGUAGE british

  SELECT CAST('2003-02-28' AS datetime);

  Server: Msg 242, Level 16, State 3, Line 4

  The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

  I wonder why that is? Our date format is dd mm yyyy, or yyyy-mm-dd, but never yyyy-dd-mm (at least I've never seen a date expressed in that form)

  Change the cast to DATE instead of DATETIME and it will work.