Are the posted questions getting worse?
- jonathan.crawford wrote:Michael L John wrote:
So, getting my 8 and 10 year old grand kids to take showers during the Covid crisis has been tough. Today, after a lot of begging, the 8 year old finally goes and gets a shower.
I'm on my weekly Teams call with my boss, and a semi-naked wet little boy comes strolling up behind me. My boss lost it. He was laughing so hard he had to end the meeting.
hahaha....sounds familiar. "You need to take a shower"....."um, how about tomorrow" *finger guns*
Well, today was "I just took a shower". I asked when. He said 'When I was at grandmas". That was 4 days ago.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
Someone has to come clean, where is the Crystal Ball?
😎
- Robert Sterbal wrote:
It takes less than 10 keystrokes to convert a column to text. (Ctrl + space, alt + 1, alt + C, t, t enter)
I'm getting the impression that you think a product for hundreds of millions of users has to work the way you want it to. If it doesn't, the product is bad.
Well for my comment, I actually would like it to do some work with this. I'm not really sure how the clipboard internals looks coming from SSMS for instance and I get that, its been a long time since I coded using the copy paste buffer for any windows api (literally 16 bit windows the last time I read a copy paste buffer with c). But man, the interpretation excel gives to any date looking thing from the copy / paste buffer is baffling, the windows programmers have obviously put much effort into interpreting this into SOMETHING because of what I see get pasted if I DON'T format the receiving cells as text.
Check it out sometime, I'd be curious if anybody knew what the excel programmers were actually trying to do with a date time value to end up with what actually gets pasted, I'm sure somebody smarter than me might have an idea. Do you?
Maybe scientific notation? Internal SQL bit patterns as a representation of date? *shrug* beats the heck out of me LOL
edit: I'd think MAYBE its because of what SSMS pokes into the buffer from an SSMS date column but then why does a text formatted cell then work fine? I remain clueless!
- Michael L John wrote:jonathan.crawford wrote:Michael L John wrote:
So, getting my 8 and 10 year old grand kids to take showers during the Covid crisis has been tough. Today, after a lot of begging, the 8 year old finally goes and gets a shower.
I'm on my weekly Teams call with my boss, and a semi-naked wet little boy comes strolling up behind me. My boss lost it. He was laughing so hard he had to end the meeting.
hahaha....sounds familiar. "You need to take a shower"....."um, how about tomorrow" *finger guns*
Well, today was "I just took a shower". I asked when. He said 'When I was at grandmas". That was 4 days ago.
Got the opposite problem, have to shower before dawn as otherwise, our single bathroom is occupied.
😎
-
Excel uses it’s own serial date system to store a date. Take a cell with a date in it and change the format to General. Time is decimal part of a day. Seem to recall it starting at 1900.
Reminds me of JDE and their version of Julian Dates.
Just wait - another Y2K type of event will happen.
-
IIRC, there is also a difference in the dates in Excel and SQL Server. Only problem is that I don't quite remember what the problem, had I haven't had much luck with my google fu to find it.
- jonathan.crawford wrote:
Excel: enable bad solutions
Access: implement bad solutions that require other bad solutions in order to function
PowerBI: Automate all the bad solutions with a bad process, and call it "data science"
I love Access, I can create giant disasters in a fraction of the time it usually takes me!
- Robert Sterbal wrote:
In nearly 30 years of using excel there are very few situations that don't have a straight forward work around.
built a report from an SQL database for a big international customer. The actual recipients of the report were their customers. So, the output was requested in the most common format - CSV.
Almost immediately they started complaining about incorrect account numbers in the report. Tirned out, 18 digit account numbers have been converted to float numbers while importing to Excel.
Can you suggest a workaround for this issue?
Apart from moving to Open Office?
_____________
Code for TallyGenerator - Lynn Pettis wrote:
IIRC, there is also a difference in the dates in Excel and SQL Server. Only problem is that I don't quite remember what the problem, had I haven't had much luck with my google fu to find it.
Perhaps you are thinking of this?
1/1/1900 is 'day zero' in SQL Server, but 'day one' in Excel.
Here some T-SQL to back that up:
DECLARE @d DATETIME = '1900-01-01 00:00:00.000';
SELECT IntDate = CONVERT(INT, SUBSTRING(CONVERT(VARBINARY(8), @d), 1, 4));The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - Phil Parkin wrote:Lynn Pettis wrote:
IIRC, there is also a difference in the dates in Excel and SQL Server. Only problem is that I don't quite remember what the problem, had I haven't had much luck with my google fu to find it.
Perhaps you are thinking of this?
1/1/1900 is 'day zero' in SQL Server, but 'day one' in Excel.
Here some T-SQL to back that up:
DECLARE @d DATETIME = '1900-01-01 00:00:00.000';
SELECT IntDate = CONVERT(INT, SUBSTRING(CONVERT(VARBINARY(8), @d), 1, 4));That is it, thank you.
- Lynn Pettis wrote:Phil Parkin wrote:Lynn Pettis wrote:
IIRC, there is also a difference in the dates in Excel and SQL Server. Only problem is that I don't quite remember what the problem, had I haven't had much luck with my google fu to find it.
Perhaps you are thinking of this?
1/1/1900 is 'day zero' in SQL Server, but 'day one' in Excel.
Here some T-SQL to back that up:
DECLARE @d DATETIME = '1900-01-01 00:00:00.000';
SELECT IntDate = CONVERT(INT, SUBSTRING(CONVERT(VARBINARY(8), @d), 1, 4));That is it, thank you.
Heh... although I don't run into it often anymore, the other problem is that Excel thinks that 1900 was a leap year unless you use the screw ball dates that have a base date of March 1st. It only has to happen once to have to know what the "work arounds" are.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Sergiy wrote:Robert Sterbal wrote:
In nearly 30 years of using excel there are very few situations that don't have a straight forward work around.
built a report from an SQL database for a big international customer. The actual recipients of the report were their customers. So, the output was requested in the most common format - CSV.
Almost immediately they started complaining about incorrect account numbers in the report. Tirned out, 18 digit account numbers have been converted to float numbers while importing to Excel.
Can you suggest a workaround for this issue?
Apart from moving to Open Office?
Only way I know is to put apostrophe before the number, it will appear when the file is loaded in excel but at least it will not be converted.
Far away is close at hand in the images of elsewhere.
Anon. - Jeff Moden wrote:Lynn Pettis wrote:Phil Parkin wrote:Lynn Pettis wrote:
IIRC, there is also a difference in the dates in Excel and SQL Server. Only problem is that I don't quite remember what the problem, had I haven't had much luck with my google fu to find it.
Perhaps you are thinking of this?
1/1/1900 is 'day zero' in SQL Server, but 'day one' in Excel.
Here some T-SQL to back that up:
DECLARE @d DATETIME = '1900-01-01 00:00:00.000';
SELECT IntDate = CONVERT(INT, SUBSTRING(CONVERT(VARBINARY(8), @d), 1, 4));That is it, thank you.
Heh... although I don't run into it often anymore, the other problem is that Excel thinks that 1900 was a leap year unless you use the screw ball dates that have a base date of March 1st. It only has to happen once to have to know what the "work arounds" are.
Did you run into those in your youth, the year looks about right 😉
😎
The last BIFF file format has two different date recordings, the Date1900 and the Date1904, 0 date is 1st of Jan 1900 and 1904 respectfully. This should not be relevant to the OOXML of the current versions, IIRC, dates are stored in the cells value section unless defined as strings, then they are moved to the internal text table and no formatting is applied. The problem with the text values is obvious, no date logic can be applied without type conversion.
Mind you, Excel is a pathological liar, often what is displayed has little resemblance to the actual value stored.
- Sergiy wrote:Robert Sterbal wrote:
In nearly 30 years of using excel there are very few situations that don't have a straight forward work around.
built a report from an SQL database for a big international customer. The actual recipients of the report were their customers. So, the output was requested in the most common format - CSV.
Almost immediately they started complaining about incorrect account numbers in the report. Tirned out, 18 digit account numbers have been converted to float numbers while importing to Excel.
Can you suggest a workaround for this issue?
Apart from moving to Open Office?
Depends on the version of Excel, current versions are fully compatible with Open / Libre Office and can use the same file standard.
😎
- David Burrows wrote:Sergiy wrote:Robert Sterbal wrote:
In nearly 30 years of using excel there are very few situations that don't have a straight forward work around.
built a report from an SQL database for a big international customer. The actual recipients of the report were their customers. So, the output was requested in the most common format - CSV.
Almost immediately they started complaining about incorrect account numbers in the report. Tirned out, 18 digit account numbers have been converted to float numbers while importing to Excel.
Can you suggest a workaround for this issue?
Apart from moving to Open Office?
Only way I know is to put apostrophe before the number, it will appear when the file is loaded in excel but at least it will not be converted.
that was the first thing I did - they were not quite satisfied.
Apostrophes had to be manually removed after importing, or all sorts of lookups they've been using would not work.
_____________
Code for TallyGenerator
Viewing 15 posts - 65,026 through 65,040 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply