6 Replies Latest reply: Oct 18, 2013 5:00 AM by dick elsman RSS

    extracting year, month, day and time form sql load script

    dick elsman

      Hi all,

       

      I am currently working on something with an script that loads data from an SQL server.

      here is a part of the code:

       

      OLEDB CONNECT TO [sql script id];

      SQL SELECT * FROM QV1.dbo."newbase_task_template_line_v";

      SQL SELECT * FROM QV1.dbo."newbase_holding_v";

      SQL SELECT * FROM QV1.dbo."newbase_ticket_strip_v";

      SQL SELECT * FROM QV1.dbo."newbase_hrm_header_v";

      SQL SELECT * FROM QV1.dbo."newbase_time_registration_v";

       

      Now, when the script loads the data, it çreates fields for the data in above standing files.

      For example, the file named newbase_time_registration has fields : 'start time'; 'end time'; 'ammount of hours spent'; etc

       

      The time and date however are not listed in fields 'month', 'year', 'week', 'day' but are all standing in one field combined.

      This is very annoying because then you would get a field value that looks like this:

      Fri May 18 2012 00:00:00 GMT+0200 (CEST)

       

      My question is: how do I extract the month, day, time and year from this?

      normally you can put

      Year('date')=Year

      but since you cannot see the field headers in the script, it seems a little harder than that.

       

      Any suggestions? please let me know

       

      Thanks in advance

       

      Kind regard

      Dick