July 10, 2015 at 2:11 pm
PROBLEM DESCRIPTION:
I have a problem where I use a batch file to call a MS SQL Script to write query results to a text file that uses a CSV extension. The file is then shared with people who use MS Excel to read the file. The problem is that the timestamp is not displayed in MS Excel 2010 as a date unless you click on the field and hit enter. For example when you open the file you will see "30:33.2" when the file is opened, but after you set focus on the field you see "7/6/2015 12:30:33 PM" in the cell contents viewer at the top of MS Excel, directly under the ribbon.
STRATEGY:
I recognize that this is more a bug than anything, but want to work around the problem by casting the timestamp as a varchar and concatenating a single quote to the beginning of the timestamp value. This will force MS Excel to display the timestamp value as a string that people will be able to easily read.
CHALLENGE:
The problem is that I know little about TransactSQL and need help casting the timestamp to a varchar in the select statement. Here's a simplified version of the query:
USE elr_reporting;
select
a.facilityid
, facilityName as Facility
, max(a.date_created) as Timestamp
, DATEDIFF(day,max(a.date_created),getdate()) as 'Days_Since'
FROM [elr_reporting].[dbo].[elr_report_dw]as a
group by
a.facilityid
, facilityName
order by
Days_Since desc,
a.facilityid
Any help would be appreciated.
July 10, 2015 at 3:10 pm
ctaylor 79909 (7/10/2015)
PROBLEM DESCRIPTION:I have a problem where I use a batch file to call a MS SQL Script to write query results to a text file that uses a CSV extension. The file is then shared with people who use MS Excel to read the file. The problem is that the timestamp is not displayed in MS Excel 2010 as a date unless you click on the field and hit enter. For example when you open the file you will see "30:33.2" when the file is opened, but after you set focus on the field you see "7/6/2015 12:30:33 PM" in the cell contents viewer at the top of MS Excel, directly under the ribbon.
STRATEGY:
I recognize that this is more a bug than anything, but want to work around the problem by casting the timestamp as a varchar and concatenating a single quote to the beginning of the timestamp value. This will force MS Excel to display the timestamp value as a string that people will be able to easily read.
CHALLENGE:
The problem is that I know little about TransactSQL and need help casting the timestamp to a varchar in the select statement. Here's a simplified version of the query:
USE elr_reporting;
select
a.facilityid
, facilityName as Facility
, max(a.date_created) as Timestamp
, DATEDIFF(day,max(a.date_created),getdate()) as 'Days_Since'
FROM [elr_reporting].[dbo].[elr_report_dw]as a
group by
a.facilityid
, facilityName
order by
Days_Since desc,
a.facilityid
Any help would be appreciated.
Would doing something like this work for you?
DECLARE @test-2 TABLE (DateField DATETIME);
INSERT INTO @test-2 (DateField) VALUES (GETDATE());
SELECT DateField, '''' + REPLACE(CONVERT(VARCHAR(23), DateField, 126), 'T', ' ')
FROM @test-2;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 10, 2015 at 3:16 pm
Is it possible to do this with a SELECT statement instead of relying upon the creation and use of an extract table?
Ideally I would like to have the conversion and concatenation happen right in the SELECT statement.
July 10, 2015 at 3:22 pm
Here's my edit
USE elr_reporting;
select
a.facilityid
, facilityName as Facility
, '-' + REPLACE(CONVERT(VARCHAR(23), max(a.date_created) as Timestamp, 126)
, DATEDIFF(day,max(a.date_created),getdate()) as 'Days_Since'
FROM [elr_reporting].[dbo].[elr_report_dw]as a
group by
a.facilityid
, facilityName
order by
Days_Since desc,
a.facilityid
Here's the error I receive
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'as'.
Please note that I changed your suggestion language to prefix a hyphen to the value so I could eliminate the challenge of working through the process of quoting a quote. I suspect I could just prefix an ASCII character code.
Please also understand that I am a total newbit to MS SQL Server, so I don't know if the declaration of a table truly creates a permanent table, or simply produces a table that exists for the duration of the runtime execution of this script.
July 13, 2015 at 7:48 am
I got it. It may not be aws elegant as it should be, but it generates the desired output.
select
a.facilityid
, a.facilityName as 'Facility'
, char(39) + REPLACE(CONVERT(VARCHAR(23), max(a.date_created), 126), 'T', ' ')as 'Timestamp_String'
-- , max(a.date_created) as 'Timestamp'
, DATEDIFF(day,max(a.date_created),getdate()) as 'Days_Since'
from [elr_reporting].[dbo].[elr_report_dw] as a
group by
a.facilityid
, facilityName
order by
Days_Since desc
, a.facilityid
http://sqlserverplanet.com/tsql/cast-date
has some great syntax examples for casting dates
July 13, 2015 at 7:53 am
I don't think that the timestamp datatype is what you think it is. You should read up about it here. https://msdn.microsoft.com/en-us/library/ms182776.aspx
Please note that timestamp is an alias for ROWVERSION. It is a horrible name and has absolutely nothing to do with dates or time as related to the clock.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 13, 2015 at 8:12 am
SSChampion, that is really intesting (and bizarre) to me.
I inherited the SQL Server solution here and thought that when I read "as Timestamp" in the DML, that I a timestamp datatype was being used. It appears that the script creator wrote something that would work and chose to reduce keystrokes instead of adhere to good form. He shold have sritten "as 'Timestamp'" using single quotes so it would be clear that the label was a label and not an argument.
<rumination> I never knew MS SQL Server would use context to know when a keywords could be overloaded as an unquoted string and used without generating a compiler or parser error. It also seems that the author of the scripts and code I inhereited seems to have a habit of relying upon these kinds of tricks to eliminate as many characters from his source code as possible - and he did this for all the code regardless of language tht was left for me. I wonder if there are other syntax peculiarities specific to MS SQL Server I should be aware of? The innate ability of MS SQL Server 2008 to to dynamically interpret overloaded keywords as a string is something I'm still trying to wrap my mind around. </rumination>
Also, do you have any recommended learning materials for MS SQL Server? If all goes well, I hope there to be budget money for MS SQL Server training for me sometime in the fiscal 2017 budget, but I'd like to get more proficiency now.
July 13, 2015 at 8:50 am
ctaylor 79909 (7/13/2015)
SSChampion, that is really intesting (and bizarre) to me.I inherited the SQL Server solution here and thought that when I read "as Timestamp" in the DML, that I a timestamp datatype was being used. It appears that the script creator wrote something that would work and chose to reduce keystrokes instead of adhere to good form. He shold have sritten "as 'Timestamp'" using single quotes so it would be clear that the label was a label and not an argument.
<rumination> I never knew MS SQL Server would use context to know when a keywords could be overloaded as an unquoted string and used without generating a compiler or parser error. It also seems that the author of the scripts and code I inhereited seems to have a habit of relying upon these kinds of tricks to eliminate as many characters from his source code as possible - and he did this for all the code regardless of language tht was left for me. I wonder if there are other syntax peculiarities specific to MS SQL Server I should be aware of? The innate ability of MS SQL Server 2008 to to dynamically interpret overloaded keywords as a string is something I'm still trying to wrap my mind around. </rumination>
Also, do you have any recommended learning materials for MS SQL Server? If all goes well, I hope there to be budget money for MS SQL Server training for me sometime in the fiscal 2017 budget, but I'd like to get more proficiency now.
You have found an excellent location for learning sql server. This site has a daily newsletter filled with excellent articles from all levels of proficiency. There is also a question of the day which can provide great learning about a vast array of subjects. The forums on here are a great wealth of information and knowledge. Try the questions, peruse the forums, post your own responses to questions as you get comfortable. Last but not least, setup a test environment at home. You can get the student edition for something like $50-75 and it is the same thing as the full blown enterprise version. This gives you an awesome spot to try stuff out, and more importantly break stuff so you have a chance to fix it in a safe location.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 13, 2015 at 8:59 am
Thanks! Also, I saw the name Steve Jones as a moderator. Do you know if this is the same Steve Jones who was a SysOpt/TechIMO moderator back in the late 90's? I have only positive memories of my interactions with him back in the day....
July 13, 2015 at 9:10 am
ctaylor 79909 (7/13/2015)
Thanks! Also, I saw the name Steve Jones as a moderator. Do you know if this is the same Steve Jones who was a TechIMO moderator back in the late 90's? I have only positive memories of my interactions with him back in the day....
Not sure if this is the same Steve Jones or not but I can assure that he is great asset to this place. And if you get the chance to meet him in person ever he is a very friendly guy who gives great presentations. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 14, 2015 at 1:04 pm
ctaylor 79909 (7/10/2015)
Please note that I changed your suggestion language to prefix a hyphen to the value so I could eliminate the challenge of working through the process of quoting a quote. I suspect I could just prefix an ASCII character code.
You can use an apostrophe character simply by "doubling" it, as shown in one of the other posts. To append/prefix an apostrophe character, just use '''' (four apostrophes - the ones on either end define this as a string, and the "doubled" apostrophes in the middle tell SQL that you want a single apostrophe character as the result.
This is an important facet of SQL (any implementation, including Oracle, DB2, etc.) and one that is really useful to know.
Examples:
set @v-2 = '''' + 'something' + '''';
-- @v-2 now holds 'something'
insert into mytable (lastname) values ('O'Brien'); -- fails
-- as the string is malformed with the single embedded apostrophe
' -- this apostrophe is just to cause the parser to end what it thinks is an open string
insert into mytable (lastname) values ('O''Brien'); -- succeeds
-- and the name O'Brien is inserted correctly
As you can see by the RED portions of the SQL code example, the first "O'Brien" insert statement causes the SQL parser to complain, while the second works fine.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply