I am very new to SQL and have this command text where this line is bringing in the year of shipped data. However Excel does not recognize the data.
I can fix this by doing a text to column and selecting General but I do not want to do this every time I open the sheet.
Is there anyway to format what is coming through as "General" recognized by Excel?
The line giving me issue is: FROM_UNIXTIME(t2.date_shipped, '%Y') AS Shipped Year,
The entire code is:
SELECT
FROM_UNIXTIME(t2.date_shipped, '%b') AS `Shipped Month`,
FROM_UNIXTIME(t2.date_shipped, '%Y') AS `Shipped Year`,
COUNT(*) AS "Count of Work Orders Shipped"
FROM
erp_workorder AS t1
LEFT JOIN
erp_shipping AS t2
ON
t1.id_primary = t2.id_workorder
WHERE
t2.date_shipped <> 0
AND
t2.date_shipped > UNIX_TIMESTAMP() - ((107000 * 30) * 30)
GROUP BY IFNULL(FROM_UNIXTIME(t2.date_shipped, '%m'), 0), IFNULL(FROM_UNIXTIME(t2.date_shipped, '%Y'), 0)
ORDER BY IFNULL(FROM_UNIXTIME(t2.date_shipped, '%Y'), 0), IFNULL(FROM_UNIXTIME(t2.date_shipped, '%m'), 0)
Thank you to anyone who can help!
Which database product are you using? And how is it interacting with Excel?
SQLTeam.com is a Microsoft SQL Server site, we're not (as) well versed in other databases.
1 Like
Queries & Connections option in Excel itself:
When you say that Excel isn't recognizing the data - what exactly does that mean? Are you getting an error - or something else?
Do you know what that function is returning - is it returning an integer, (n)varchar, (n)char or some other data type?
I do not know what function the SQL code is returning, all I know is the format that comes through excel does not recognize it in formulas.
I have the below formula but get a #N/A error:
However if I convert the data/column using Text to Columns and select General,
then the formula works no problem:
I do not want to have to do this every time the data refreshes on the Query however so I want to know if there is a way to bring it in with the correct format.
I hope this explains everything. I am very very new to SQL
Thank you very much for any help you can provide!
The problem doesn't appear to be the data - the problem is in the formula. That formula is expecting the year to be in a non-text format and fails because the column is defined as text.
If you are just refreshing the data in Excel - then the column data type shouldn't change. But - it sounds like you are not actually modifying the columns data type.
This doesn't appear to be an issue on the SQL side - rather a problem with the column definition in Excel and/or the formula. I would try fixing the formula so it converts the value from that column to a number - which can be done using VALUE or adding 0 to the column.
Thank you very much. An excel forum led me here stating it was an issue with how the query was being brought in. Adding value to the formula fixed the issue perfectly!
Thank you very much!!
Out of curiosity is there a way to bring in that data as a number? Like a format option in the SQL code?
That is why I was asking how that UNIX_TIMESTAMP function worked. With that said, you could cast/convert to an integer - something like:
CAST(FROM_UNIXTIME(t2.date_shipped, '%Y') As Shipped Year
However, that probably wouldn't change anything because the column in Excel is defined as text and regardless of how SQL is returning the data the column will convert it to text.
I am glad to hear using VALUE worked, thank you for the feedback.
