date format and implementation

I have the following script  

@cStartDateIn CHAR(10) = '01/01/9999', -- dtmManualRunBeginDate in the DTS Global Variables paid date
@cEndDateIn   CHAR(10) = '01/01/9999', -- dtmManualRunEndDate in the DTS Global Variables
@cStartDtIncr CHAR(10) = '01/01/9999', -- incurred date
@cEndDtIncr   CHAR(10) = '01/01/9999',
@StartDateOut CHAR(10) = '01/01/9999' OUTPUT,
@EndDateOut CHAR(10) =  '01/01/9999' OUTPUT

   --SET @sRunDt = @cLastRunDate           
   SET @dtDateStart    = CAST(@cStartDateIn AS DATETIME)
   SET @dtDateEnd      = CAST(@cEndDateIn AS DATETIME)
   SET @dtDateStartInc = CAST(@cStartDtIncr AS DATETIME)
   SET @dtDateEndInc   = CAST(@cEndDtIncr AS DATETIME)

     IF @cFreq = 'M'
       BEGIN
        SET @dtDateStart  =  DATEADD(mm, DATEDIFF(mm,0,@CurDt) - 1, 0) -- First day of previous month
        SET @dtDateEnd =  DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,0,@CurDt), 0)) --Last day of previous month
        SET @dtDateStartInc = '01/01/2014'
        SET @dtDateEndInc   = @dtDateEnd

that runs between the first and last day in a month now I want it to run from 

01/01/2014 to the 5th of every month how do I implement that?

and the script is filtered by this date time field

   WHERE (     clh.CLHDRLastActnDate BETWEEN @dtDateStart   AND @dtDateEnd

This 

March 30th, 2015 7:07pm

Can you specify your requirements in detail? Thanks.

Here is a date sequence generator:

DECLARE @Start DATE ='20140105'; 

WITH ctesequence ( seqno) 

     AS (SELECT 0 

         UNION ALL 

         SELECT seqno + 1 

         FROM   ctesequence 

         WHERE  seqno < 12 - 1) 

SELECT [FifthOfMonth]=Dateadd(mm, seqno, @Start) 

FROM   ctesequence;
/*
FifthOfMonth
2014-01-05
2014-02-05
2014-03-05
2014-04-05
2014-05-05
2014-06-05
2014-07-05
2014-08-05
2014-09-05
2014-10-05
2014-11-05
2014-12-05
*/

Avoid string dates as much as possible.  Use DATE/DATETIME instead.  Use ANSI date literal format.

Example: 

@cStartDateIn DATE = '99990101'

DATE/TIME functions: http://www.sqlusa.com/bestpractices/datetimeconversion/

Free Windows Admin Tool Kit Click here and download it now
March 30th, 2015 7:46pm

Hi Dre01,

I see pieces of code and no script

1. Please post the script that you are using instead of explaining it. we know how to read code :-)

If your script use any table then please post queries to create the relevant tables and insert some sample data for testing and discussion.

2. Basically you will need to use a numbers table or a dates table (those are accessories tables that you should create in each instance for improving queries like this one). Using the numbers/date table you can select all the dates that you need (from the 01/01/2014 to the 5th) and using cross apply with your original query you can execute the query on those dates

* by the way, you can get those dates dynamically each execution using loop (as Kalman did, using recursive CTE), but it is better to use numbers/dates tables for most cases. In any case since you need only 5 specific values (from the 01/01/2014 to the 5th) you can just use hard coded values like this:

DECLARE @Start DATE ='20140105'; 
WITH ctesequence ( seqno) 
     AS (SELECT * from (VALUES (1),(2),(3),(4),(5))T(s)) 
SELECT [FifthOfMonth]=Dateadd(mm, seqno, @Start) 
FROM   ctesequence;

* In any case there is no need to loop :-)

March 31st, 2015 1:33am

The file will be sent on the 5th day of each month to xxxx FTP and placed in a folder .

Each time the extract is run, data will be included from 01/01/2014 to date of extract because this is a snapshot of the data.

The varables I have above is from a template but the template is built for various frequency of run e.g Quaterly(Q)

Annually(A)--

But the one I posted was based on a monthly run hence

     IF @cFreq = 'M'
       BEGIN
        SET @dtDateStart  =  DATEADD(mm, DATEDIFF(mm,0,@CurDt) - 1, 0) -- First day of previous month
        SET @dtDateEnd =  DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,0,@CurDt), 0)) --Last day of previous month
        SET @dtDateStartInc = '01/01/2014'
        SET @dtDateEndInc   = @dtDateEn

But to tailor it to my requirement it is from 01/01/2014 to the 5th of current month.

Free Windows Admin Tool Kit Click here and download it now
March 31st, 2015 3:25pm

and the variable curr date is 

 DECLARE  @CurDt                 DATETIME 

  SELECT @CurDt          = GETDATE() 

March 31st, 2015 3:27pm

This is my main interest

SET @dtDateStart  =  DATEADD(mm, DATEDIFF(mm,0,@CurDt) - 1, 0) -- First day of previous month
        SET @dtDateEnd =  DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,0,@CurDt), 0)) --Last day of previous month
        SET @dtDateStartInc = '01/01/2014'

Free Windows Admin Tool Kit Click here and download it now
March 31st, 2015 3:31pm

I am thinking its going to be

      IF @cFreq = 'M'
       BEGIN
        SET @dtDateStart  =  '01/01/2014'
        --DATEADD(mm, DATEDIFF(mm,0,@CurDt) - 1, 0) -- First day of previous month
        SET @dtDateEnd =  @CurDt
        --dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,@CurDt), 0)) --Last day of previous month
        SET @dtDateStartInc = '01/01/2014'
        SET @dtDateEndInc   = @dtDateEnd
       END   

March 31st, 2015 3:50pm

In addition to what the others are already helping you with. You don't want to use "BETWEEN" with dates. You can google for the specifics as there are already a ton of posts detailing why, but high level - you could end up skipping data that you would want retrieved based on how "BETWEEN" interacts with date datatypes.
Free Windows Admin Tool Kit Click here and download it now
March 31st, 2015 3:57pm

Good day Dre01,

Did you read my response?

March 31st, 2015 5:35pm

>> I have the following script  <,

how soon can you fix this mess? The data type prefixes are a design flaw called a tibble; they violate the basic rule that we do not mix data and meta data in a schema. Please learn the basic ISO-11179 naming rules. 

Next, we have a DATE datatype in T-SQL, which matches ANSI/ISO Standard SQL. Why CHAR(10)? We used strings in 1970's COBOL! The one and only date display format is ISO-8601, which means yyyy-mm-dd; but you picked an ambiguous local dialect! This

Finally, SQL is a declarative language, so we do not like to use local variables. We aklso hate loops and if-then logic. Why cast the strings to the old Sybase DATETIME and not even the current DATETIME2(0)when you are using DATE? 

There is no generic (start_date, end_date) in RDBMS; it has to be an attribute to something in particular. When you said dt in what the ISO-11179 standards call the role, I read this as short for delirium tremens alcohol withdrawal. I used to work health care and criminal justice. Also, a column is not a field; the only place this term is used in SQL is as the components in temporal data types. 

Since SQL is a database language, we prefer to do look ups and not calculations. They can be optimized while temporal math messes up optimization. A useful idiom is a report period calendar that everyone uses so there is no way to get disagreements in the DML.
 
The report period table gives a name to a range of dates that is common to the entire enterprise. Your weird date range is a minor problem, but here is a quick guess at it. 

CREATE TABLE Months_Since_2014
(months_since_2014_name CHAR(10) NOT NULL PRIMARY KEY
   CHECK (months_since_2014_name LIKE ??),
 month_5_start_date DATE NOT NULL,
 month_5_end_date DATE NOT NULL,
  CONSTRAINT date_ordering
    CHECK (month_5_start_date <= month_5_end_date),
 etc);

These report periods can overlap or have gaps. You can use a BETWEEN with this table is you do it right. 
Free Windows Admin Tool Kit Click here and download it now
March 31st, 2015 5:42pm

If the end date is the 5th of every month, then it would be:

DECLARE @day VARCHAR(2)
SET @day='05'
SELECT 
	CAST (CAST(YEAR(GETDATE()) AS VARCHAR) 
	+ '-' + 
	CAST(MONTH(GETDATE()) AS VARCHAR)
	+ '-' + 
	@day  AS DATETIME) AS Enddate

Thanks & Regards,

Praveena.


April 1st, 2015 12:25pm

I am using what we call a common table based on what is used in the table for frequency e,g 'Y' for Year,D for Days, and in my case 'M' for Months.

  IF @cFreq = 'M'
       BEGIN
        SET @dtDateStart  =  DATEADD(mm, DATEDIFF(mm,0,@CurDt) - 1, 0) -- First day of previous month
        SET @dtDateEnd =  DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,0,@CurDt), 0)) --Last day of previous month
        SET @dtDateStartInc = '01/01/2014'
        SET @dtDateEndInc   = @dtDateEn

But we missed the first 3 months and they want from the first of January to the last day of last month.

What is the modification to this

SET @dtDateStart  =  DATEADD(mm, DATEDIFF(mm,0,@CurDt) - 1, 0) -- First day of previous month
        SET @dtDateEnd =  DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,0,@CurDt), 0)) --Last day of previous month
        SET @dtDateStartInc = '01/01/2014'

Thanks.

Free Windows Admin Tool Kit Click here and download it now
April 10th, 2015 12:46pm

Don't border folks I got it.

Thanks

April 10th, 2015 1:13pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics