Hi all,
Phil Wade helped me with the SQL for this query the other week and it worked fin, until now! It goes like this:-
A non-SI based gINT setup for concrete cube testing. Data about each cube is stored in the CUBEDATA table, including Date created, date received and date tested and the cube type. The results of the test are stored in the child table CUBERESULTS, single row of data for both parent and child data. At Output, a User Report Variable prompts for the date a cube was tested, and only outputs any cube results that were tested on that date.
This all works fine. The area I'm having a huge problem with is the data in part of the report header. In the header I want to list the Date Sampled, the Date Received, the Date Tested and the Sample type of all the results on that report. I have been doing this by using the setup below:-
Date Sampled: <<SqlList(,Select Distinct [CUBEDATA].[CubeDateS] From [CUBEDATA] Where_ [CUBEDATA].[PointID] = '<<PointID>>' And [CUBEDATA].[CubeTestDt] =_ #<<Format(<<Report Var.Prompt>>,dd/mm/yyyy)>>#)>>Date Received: <<SqlList(,Select Distinct [CUBEDATA].[CubeDater] From [CUBEDATA] Where_ [CUBEDATA].[PointID] = '<<PointID>>' And [CUBEDATA].[CubeTestDt] = _ #<<Format(<<Report Var.Prompt>>,dd/mm/yyyy)>>#)>>Date Tested: <<Report Var.Prompt>>Sample Type: <<SqlList(,Select Distinct [CUBEDATA].[CubeSampT] From [CUBEDATA] Where_ [CUBEDATA].[PointID] = '<<PointID>>' And [CUBEDATA].[CubeTestDt] = _ #<<Format(<<Report Var.Prompt>>,dd/mm/yyyy)>>#)>>
This has been working fine up until today, when our lab manager made me aware that the only data that was outputting in the header was the Date Tested.
After much faffing around I have discovered that everything works fine if the date is 12th of the month or greater, but outputs nothing (except the date tested) if the date is between 1st and the 11th. This obviously has something to do with date formatting but I have no idea what.
All fields in the Data Template are format of dd/mm/yy, along with the prompt.
Help!!! :D
SQL language expects dates in the format MM/dd/yyyy, you need to change the format strings to this.
Phil WadeDatgelBentley Channel Partner and Developer PartnerE: phil.wade@datgel.com | T: +61 2 8202 8600 & +65 6631 9780
Get the most out of gINT with Datgel Tools.
Thanks again Phil. I was reading up on SQL date formats yesterday, and I was damn sure I had tried that...
Just looked up the page I was reading yesterday, and it states that SQL uses YYYY-MM-DD! (and I definately tried that) That will learn me to trust stuff on the interweb... (well, apart from these forums) DOH! :D
Different derivatives of SQL require different formats. I think YYYY-MM-DD is correct for SQL Server.