locked
First of November previous year RRS feed

  • Question

  • Sorry guys, any idea how I can setup the first of November of the previous year dynamically? So running now it should be 01-11-2014 but if I run in 2th of January 2016 it should be 01-11-2015

    Any clue? Thanks


    • Edited by Jingyang Li Friday, October 9, 2015 4:05 PM fixed a typo
    Friday, October 9, 2015 2:58 PM

Answers

  • Try

    select cast( CAST(datepart(year, CURRENT_TIMESTAMP) - 1 as char(4)) + '1101' as datetime) as LastNovember1


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by DIEGOCTN Friday, October 9, 2015 3:53 PM
    Friday, October 9, 2015 3:26 PM
  • Select Dateadd(month,10 ,DATEADD(year,DATEDIFF(year,0,Current_timestamp)-1,0)) 
    • Marked as answer by Eric__Zhang Tuesday, October 13, 2015 7:46 AM
    Friday, October 9, 2015 4:04 PM

All replies

  • Simply use a case statement, e.g. if datepart(month, @Today) < 11 then ... else ... end

    Your explanation is a bit unclear as you show 01/01/2015 and not first of November.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, October 9, 2015 3:05 PM
  • How about:

    Declare @d Date

    Select @d = convert(date, '01-11-' + Datepart(YYYY, getdate()))


    Andy Tauber
    Data Architect
    The Vancouver Clinic
    Website | LinkedIn

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Friday, October 9, 2015 3:06 PM
  • Thanks everyone. Naomi I need to put a where in a code within SSIS. So I need to parameterize this date. I am using

    Dateadd(month,datediff(month,0 ,getdate())-11,0)

    but it works just because we are now in October 2015 (before I meant 01-11-2015, I missed a 1)

    Andy same reason because it's hard to use your code


    • Edited by DIEGOCTN Friday, October 9, 2015 3:19 PM
    Friday, October 9, 2015 3:14 PM
  • If you set the date change with the running date, you can simply add a WHERE clause without sending in any parameter.
    Friday, October 9, 2015 3:21 PM
  • Try

    select cast( CAST(datepart(year, CURRENT_TIMESTAMP) - 1 as char(4)) + '1101' as datetime) as LastNovember1


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by DIEGOCTN Friday, October 9, 2015 3:53 PM
    Friday, October 9, 2015 3:26 PM
  • this?

    DATEADD(mm,DATEDIFF(mm,0,GETDATE())-11,0)


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, October 9, 2015 3:28 PM
  • this?

    DATEADD(mm,DATEDIFF(mm,0,GETDATE())-11,0)


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    This what I am using now but I can't use in February for example.
    Friday, October 9, 2015 3:52 PM
  • Did you see my answer? If you're using SQL Server 202 and up, then you can also use DATEFROMPARTS function.

    -- SQL 2000-2008
    
    select cast( CAST(datepart(year, CURRENT_TIMESTAMP) - 1 as char(4)) + '1101' as date) as PreviousYearNovember1
    
    
    -- SQL 2012+
    select DATEFROMPARTS(datepart(year, CURRENT_TIMESTAMP)-1, 11, 1) as PreviousYearNovemeber1


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi N Friday, October 9, 2015 3:55 PM
    Friday, October 9, 2015 3:53 PM
  • Thanks Naomi, I was thinking something similar. It's perfect.
    Friday, October 9, 2015 3:54 PM
  • Select Dateadd(month,10 ,DATEADD(year,DATEDIFF(year,0,Current_timestamp)-1,0)) 
    • Marked as answer by Eric__Zhang Tuesday, October 13, 2015 7:46 AM
    Friday, October 9, 2015 4:04 PM
  • You should follow ISO-8601 rules for displaying temporal data (https://xkcd.com/1179/). Avoid dialect in favor of ANSI/ISO Standard SQL, so you do not look like a hillbilly :)

    If you are a 1970's COBOL programmer, you will use string functions like CAST(), CONVERT(), DATEPART() concatenation, etc to built your a data in a string. Over and over and over. 

    If you are an SQL programmer, you will built a look-up table for a few years and use it. It will be created once and take less space than procedural code. 

    Here is a trick for creating a table constant. The casting in the first row assures the data types of the column. 

    CREATE VIEW Prior_Novembers
    AS
    SELECT X.year_start_date, X.year_end_date, X.prior_november
      FROM (VALUES
            (CAST('2015-01-01'AS DATE),
             CAST('2015-12-31'AS DATE), 
             CAST('2014-10-01' AS DATE)),
            ('2014-01-01', '2014-12-31', '2013-10-01'),
            ('2013-01-01', '2013-12-31', '2012-10-01'))
             AS X(year_start_date, year_end_date, prior_november)


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, October 11, 2015 3:12 AM
  • Sorry Joe, but your solution is a little bit slow. And not totally clear, at least for me. I do prefer to use a simple "where" to be honest.
    Thursday, October 15, 2015 12:59 PM
  • this?

    DATEADD(mm,DATEDIFF(mm,0,GETDATE())-11,0)


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    This what I am using now but I can't use in February for example.

    sorry didnt understand what you mean by it

    Its working correctly according to me

    passing Feb 2015 date will fetch you Mar 2014 which is as per rule

    see

    SELECT DATEADD(mm,DATEDIFF(mm,0,'20150201')-11,0)


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, October 15, 2015 1:31 PM
  • Use an anchor date that happend to be Nov 1st (e.g. 19001101) and add many times as needed the exact 12 months period.

    SELECT
        DATEADD(MONTH, (DATEDIFF(MONTH, '19001101', CURRENT_TIMESTAMP) / 12) * 12, '19001101') AS [Prv Nov 1st inclusive] AS col1;



    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    Thursday, October 15, 2015 2:58 PM
  • Check out my article on calendar tables.

    Build it once, use it forever. You could fairly easily incorporate Naomis solution as a column.


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Thursday, October 15, 2015 3:16 PM
  • I am showing you one way to write an auxiliary table that will port, be shared by other sessions and will run much faster than re-computing the same constants over and over and over, thousands of times with procedural code for hundred of rows. 

    When you are creating a look-up or auxiliary table like this, ask yourself, “Is the data dynamic or static?” and “is the volume large or small?”

    In your case, the date you want in every year is static. There is only one “first of November of the previous year” for each year. How many years worth of Novembers do you need? I will guess you application is happy with 100 years of data.

    The DATE data type is 3 bytes. For quick approximation, round up and assume that each row is 10 bytes. So this table will be less than auxiliary table 1000 bytes. This will fit into main storage or cache on even a home computer. 

    This is a conceptual leap. SQL is a database language, not a computational language. It is designed for JOINs, predicates and that kind of stuff, but stinks on math, display formatting and temporal math.  Think sets and data, not strings and computations. 

    Remember that the Sybase DATETIME temporal functions all come UNIX floating point numbers and clock ticks on DEC hardware. Microsoft is trying to get rid of the old code museum with the new DATE, TIME, and DATEIME2(n) data types, but it will take awhile because of legacy code (aka Family Curse) and legacy mindsets. Look at how many people still use the old UNIX getdate() function instead of the correct CURRENT_TIMESTAMP today! 

    On top of this, we had COBOL to poison us. Please don't tell me that you never wrote COBOL; it gave you a particular mindset. You never spoke Latin either, but you use that alphabet and their vocabulary in all the major languages of the Western world. 

    COBOL uses strings for dates, so of course old Sybase T-SQL had CONVERT(), DATEPART(), etc. instead of a true temporal data type. Look at how many of the proposed procedural answer to your question are based on “date → display string → special string functions → date” transforms! This is like taking “integer → Roman Numerals → special string functions → integer” because the compiler did not have  proper data type support. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, October 15, 2015 4:07 PM