Temporal Types EXTRACT

• venusjesse

  Grasshopper

  Points: 17

  More actions

  September 12, 2008 at 3:34 pm

  #382632

  I am an student, not a developer, so pls. HELPPPPPPP

  The query stated in the book is as follow:

  SELECT name, (EXTRACT(YEAR FROM dateadded) AS year,

  EXTRACT ( MONTH FROM dateadded + INTERVAL '30' DAY)

  AS Month

  FROM items;

  The only way it works with the exception of adding the 30 days to the month is as follow:

  SELECT name,YEAR(dateadded) AS Year,

  MONTH(dateadded)AS month

  FROM items

  WHERE DATEADD(month,1,dateadded)< GETDATE()

  The other option it incremented the 30 days, but includes the year and day ( which I do not need)

  DATEADD (month,1, dateadded)

  Chicken Salad19981998-12-13 00:00:00.

  How can I get rid of the day and year for month?

  Thank you

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  September 12, 2008 at 3:53 pm

  #871376

  Check out the DATEPART and DATENAME functions.

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

• wim.buyens

  Ten Centuries

  Points: 1159

  More actions

  January 28, 2010 at 8:09 am

  #1110059

  I'm not sure what you want but you could use:

  SELECT YEAR('2010-03-22'),MONTH(DATEADD(m,1,'2010-03-22'))

  or you could take year month and add 01 to create a string that you convert to datatime.

  To find ast dates of the month of your value.. get the next month and substract 1 day.

  For instance:

  -- select * from [System].[fn_Months]('2008-01-01',10) will give you 10 months starting from @Date

  CREATE FUNCTION [System].[fn_Months](@Date datetime,@n AS BIGINT)

  RETURNS TABLE

  WITH SCHEMABINDING,ENCRYPTION

  AS

  RETURN

  WITH

  L0 AS(SELECT 1 AS c UNION ALL SELECT 1),

  L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),

  L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),

  L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),

  L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),

  L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),

  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)

  SELECT DATEADD(m,n-1,CAST(CAST(YEAR(@date) AS nvarchar(4))+'-'+CAST(MONTH(@date) AS NVARCHAR(2))+'-01' AS DATETIME)) StartDate,

  DATEADD(d,-1,DATEADD(m,n,CAST(CAST(YEAR(@date) AS nvarchar(4))+'-'+CAST(MONTH(@date) AS NVARCHAR(2))+'-01' AS DATETIME))) EndDate

  FROM Nums WHERE n <= @n;