Output of Empty Columns in SQL Query

Question

I have a fairly large SQL Query that converts a numeric date data type to a date time value and further specifies the search based on my requirements. However when I run this query I'm seeing the column names but there is no data in the columns, it is just the titles of the columns in this order: Dataset, Date, Time, MsgID, Parms, Dataset When the order should be: Data, Time, Dataset, Media (Separate column with Substring of Parms). Also this is only showing my data from the past 2 days instead of the entire database, which also is a problem.

I need some help in displaying the columns in that order with the data present from all of the columns. If anyone can make any suggestions or modificiations to my existing SQL query to get the required output, it would be greatly appreciated. I know this is a quick fix for an expert programmer, but I'm still learning the ropes and require some assistance.

This is my SQL Query:

    SELECT [Object] AS [Dataset],
    CAST(DATEADD(HOUR,-4,CONVERT(DATETIME,LEFT([Date],8)+' '+
    SUBSTRING([Date],10,2)+':'+
    SUBSTRING([Date],12,2)+':'+
    SUBSTRING([Date],14,2)+'.'+
    SUBSTRING([Date],15,3))) AS DATE) 'Date',
    LEFT(CAST(DATEADD(HOUR,-4,CONVERT(DATETIME,LEFT([Date],8)+' '+
    SUBSTRING([Date],10,2)+':'+
    SUBSTRING([Date],12,2)+':'+
    SUBSTRING([Date],14,2)+'.'+
    SUBSTRING([Date],15,3))) AS TIME),8) 'Time',
    MsgId,
    Parms,
    CASE WHEN MsgID = '61' THEN SUBSTRING(Parms,35,6)
    ELSE '' --Optional ELSE
    END  AS [Dataset]
    FROM ( SELECT  ItemId,
    CONVERT(VARCHAR(18),[Date]) [Date],
    [Object],
    MsgID,
    Parms
    FROM JnlDataSection
    WHERE CAST(substring(convert(varchar(50), [Date]), 0, 5) + '-' +
    substring(convert(varchar(50), [Date]), 5, 2) + '-' +
    substring(convert(varchar(50), [Date]), 7, 2) AS DATETIME) =
    CONVERT(date, DATEADD(day, -1, getdate()))) A --Converting to date again to          remove     the time part
    WHERE SUBSTRING(Parms,35,6) = 'X05219' AS [Media]
    ORDER BY [DATE] DESC;

Please Note: I'm using SQL Server Management Studio 2008.

try running it without the Where clause, if you're getting results it's the condition... — Milen
– Milen, Commented Jul 15, 2013 at 16:25
I'd comment the entire lot: WHERE CAST(substring(convert(varchar(50), [Date]), 0, 5) + '-' + substring(convert(varchar(50), [Date]), 5, 2) + '-' + substring(convert(varchar(50), [Date]), 7, 2) AS DATETIME) = CONVERT(date, DATEADD(day, -1, getdate()))) A --Converting to date again to remove the time part WHERE SUBSTRING(Parms,35,6) = 'X05219' AS [Media] to see if that would make a difference.. — Milen
– Milen, Commented Jul 15, 2013 at 16:29
@milenpavlov I did that and it is showing results, but the Parms is not trimmed anymore and msgid = 61 is not anywhere. — omar K
– omar K, Commented Jul 15, 2013 at 16:29
@omarK: You could create a view in that table and return normal datetime values in the view. That way it would be much easier to work with your data. — Andriy M
– Andriy M, Commented Jul 15, 2013 at 18:24

BWS · Accepted Answer · 2013-07-15 16:29:35Z

-1

Now, it looks like your extraction from [DATE] into Date and Time is incorrect. You are putting the same string into 'Date' and 'Time'

answered Jul 15, 2013 at 16:29

BWS

3,85620 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

omar K Over a year ago

Is it possible for you to show me the correct syntax in my sql query?

BWS Over a year ago

I don't know the source of your data, nor what you are parsing into the Date and Time string. If you look at what you are calling "Date", it is using the same subtstring that you are using for time. My guess would be a different part of the DATE string, and also be using "/" instead of ":" as seperators.

Collectives™ on Stack Overflow

Output of Empty Columns in SQL Query

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related