locked
filtering getdate with time stamps RRS feed

  • Question

  • Hi All,

    I have something like below requirement.

    Want to generate CSV file with below criteria - It has to generate 4 times a day using below criteria :-

    I want it 4 times a day :
    6AM  , contains sales from 00:00  AM - 05.59 AM of the date
    12PM ,contains sales from 06:00  AM - 11.59 AM of the date
    6PM,  ,contains sales from 12:00  PM - 05.59 PM of the date

    00AM ,contains sales from 06:00 PM - 11.59 PM of the date

    The above dates are as per the sales invoice date which need to be filtered 4 times a day using above time slots.

    Any idea on this ?

    Thanks,

    Thursday, February 25, 2016 5:01 AM

Answers

All replies

  • Yes

    You have multiple ways to generate CSV from SQLServer

    1. SSIS package

    2. bcp

    3. OPENROWSET

    see

    https://bytes.com/topic/sql-server/answers/689170-export-data-csv-text-file

    http://www.sqlservercurry.com/2011/01/sql-server-export-table-to-csv.html

    You can use any one of the above methods and schedule the script/package through SQL agent job. There will be four daily schedules at 12 AM, 6 AM, 12 PM and 6 PM.

    Then it will make sure the script/package gets executed generating required file at the above times


    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, February 25, 2016 5:58 AM
  • Hi Ultra,

    You can achieve by using SSIS package

    1.Create SSIS package to load data into CSV file

    2. Check if CSV file with Current date is exist or not. If not exist then create SSIS package with Today's date.

    3. Use that file as Destination and load the data by passing Sales invoice time based 

    4. Create a SQL job and schedule to run at 6AM, 12PM, 6PM, 00AM. 

    5. Sales invoice time can be populated based on the job schedule time.

    For E.g If job is triggered at 6 AM then we can get the current time and based on the time we can get the sales time (in this case 00-05.59 AM)

    We can implement the logic in package itself


    Please mark as answer if my post is helped to solve your problem

    and vote as helpful if it helped so that forum users can benefit 


    Srinivasarao

    Thursday, February 25, 2016 6:13 AM