November 30, 2014 at 7:11 am
Hi!
Table:
CREATE TABLE [dbo].[Weight_table](
[Weight_id] [int] IDENTITY(1,1) NOT NULL,
[dato] [datetime] NULL,
[weight] [decimal](7, 2) NULL,
[Comment] [varchar](250) NULL,
[linepoint] [decimal](7, 2) NULL,
CONSTRAINT [PK_Weight_table] PRIMARY KEY CLUSTERED
(
[Weight_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
SELECT CAST(dato AS FLOAT) from dbo.Weight_table
Gives:
(No column name)
41956
41960
41961
41970
41962
41964
41965
41965
41967
41968
41969
41971
Which is exaxctly what i want.
But how to convert 41960 to a DATETIME?
And how to convert '20141130' to a number?
And how could i have found out without asking here....
Best regards
Edvard Korsbæk
November 30, 2014 at 7:31 am
... Why? ...
Why do you want to convert numbers to dates and back?
Oh, and how you do it is pretty much the way you showed in your example
SELECT CAST(41960 AS DATETIME)
SELECT CAST(CAST '20141130' AS DATETIME) AS FLOAT)
Do note that neither of these will work with the newer date data types (DATE, DATETIME2, DATETIMEOFFSET, etc)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 30, 2014 at 9:49 am
Edvard Korsbæk (11/30/2014)
And how could i have found out without asking here....
There are a huge number of examples in "Books Online", affectionately and commonly referred to as just "BOL", which is the "Help" system for SQL Server. There, you would lookup "dates [SQL Server]" and peruse the many entries there.
Unfortunately, BOL is no longer included on the installation media of SQL Server. You just about have to have some prior knowledge that it exists and then you have to download it and install it separately. While you can certainly find the same information online on the MSDN and TechNet sites, I prefer BOL for two reasons...
1. The index that is included in BOL makes it (IMHO) a lot easier to find things than the online methods do and...
2. I sometimes work on systems where internet connections aren't allowed.
BOL also doesn't necessarily cover the "tricks of the trade" or it sometimes covers such a trick in an awful manner as it did for calculating the ISO Week in 2005.
For the 5 digit numbering when you convert a DATETIME to things like FLOAT, it's a different story. Older versions of BOL used to explain a thing that any really good user of EXCEL might be aware of and that's knowledge of a thing called a "DATESERIAL" number, which is the number of days that have passed since midnight on the first of January, 1900. BOL also used to even explain that the underlying 8 byte value was actually made from two 4 byte integers, one to hold the number of days since 1/1/1900 and one to hold the number of 1/300 of a second time slices to represent the time. The DATETIME data-type also allows addition and subtraction, which can be VERY handy at times, whereas the newer temporal data-types don't even allow an explicit conversion. For example, the following code...
SELECT CAST(GETDATE() AS FLOAT); --DATETIME data-type
GO
SELECT CAST(CAST(SYSDATETIME() AS DATE) AS FLOAT); --DATETIME2(7) data-type
GO
SELECT CAST(CAST(SYSDATETIME() AS DATE) AS FLOAT); --DATE data-type
GO
... returns the following (1 good answer followed by 2 errors in the order given above)...
----------------------
41971.4764343364
(1 row(s) affected)
Msg 529, Level 16, State 2, Line 1
Explicit conversion from data type date to float is not allowed.
Msg 529, Level 16, State 2, Line 1
Explicit conversion from data type date to float is not allowed.
Why might that be important? I can't speak for anyone else but I find it easier to calculate the difference between two dates and display the difference in hours, minutes, and seconds much easier by doing direct subtraction rather than going through the pain of calculating seconds and then reformatting to an hhhh:mi:ss format, as is so often done for timecard, telephone, and other forms of billing by duration. Although converting to seconds isn't THAT difficult, for fairly long durations, the conversion to seconds can sometimes lead to an error. With direct subtraction, you can have durations or total durations up to 10,000 years - 3 milliseconds.
Here's an article on that subject...
http://www.sqlservercentral.com/articles/T-SQL/103343/
So, to answer your question of "And how could i have found out without asking here....", the answer is, "It Depends". As with so many frustrating things in the world of computers, sometimes you need to know a lot about what you're looking for before you can find it. Asking here is usually a really good way to relieve some of that frustration. 🙂
Shifting gears a bit, I join Gail in asking "why" you want to convert whole dates and times to and from what boil down to INTEGERS. Although the MSDB database still stores such a problem in some of the job related tables, I wouldn't ever store such data as a permanent entry into a table anywhere.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 30, 2014 at 10:12 am
First:
THANKS!
Second:
Because i have to do a lot of calculations with the dates.
I have made a graph of my ongoung process- trying to get back to the weight I had, when i were 30 (That was in 1965!).
OK, i have calculated the best straight line through my weight over the last 3 month's by 'Least squares method'.
That gave me a formula, where i can calculate, which day i get to 80 kg. again if nothing happens based upon the last 3 months.
But to set something up as Weight = '20141130'*-0.943245 + 96.57 is not good.
You need a number representing the date.
Thats why.
November 30, 2014 at 10:19 am
Heh... I definitely sympathize with that task. For me to get back down to what I once weighed, I'd have to loose the equivalent of a 9th grader. :pinch: 😛
Shifting gears back to the subject, since EXCEL will allow you to easily plot "trend lines", wouldn't it be easier to do all of this in a spreadsheet especially since the number of data points are pretty small?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 30, 2014 at 2:54 pm
Edvard Korsbæk (11/30/2014)
Because i have to do a lot of calculations with the dates.
Look up the DATEADD and DATEDIFF functions
You need a number representing the date.
Thats why.
So something like the number of days since the beginning of this year - DATEDIFF(dd,'2014-01-01',GETDATE())
Seriously, learn the date functions and use them. The conversion you came up with works with DATETIME but will fail with DATETIME2. The functions work with both.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 1, 2014 at 1:34 pm
This is very efficient way to make sure, that one of the beginners never get better.
OK - This was a a small sample to get better - Of coause this could have been made faster and easier with pen & pencil.
But, I try to get better, and one of the sources is here.
1. Putting “table” in a table is so bad it is called “tibbling” in SQL code smells. (But absolutely no explanation of why. I love to use names i can remember, and says something of what the contennt is.)
2. Why do you think that the count of physical insertions to one tab le on one machine can be a key?
3. Think about how silly a “weight-id” is as a concept! What is the identifier of “5.5 Kg” in your world? A weight is a measurement,and you do not understand how data works.
I use the sql database as a container for my data. And my frontend has a bad day, if there is no primary key. As a good practise, I allways make a not user editable (Or visible!) field for the primary key.
4. What the heck is a “line_point”?
A name - nothing else. I used it for setting the values for my line
5. Why dd you use DATETIME? Did you weigh yourself every microsecond?
Allway use datetime, and each and every table i have inherited uses it. Not exactly easy to use (As my example shows), but makes a table readable for everybody, and not just my frontend, which uses days since 1.th january 1801 as dates.
6. Why do you have more NULL-able columns than entire payroll systems in major corporation?
Why not??
7. Why did you use DECIMAL(7,2)? How fat are you? Very few people are over a metric ton! But if you allow garbage data, you will get it.
No particular reason to make it smaller. If i need a validation of data, i do it in the front end. Now and again you get surprised over, how big data can get. I make the payroll for app. 1000 employee's at one location, and their off-day-in-liey balance is saved in 1/100 of a second (which is nessecary, ehen you add a lot of inexact values, you get a hell of an error in the end!). When you summirize over that, my INT was too smal, and to change the table to BIGINT was not as trivial as having used it from the start.
Your table cannot hold two weights the same day, and anyway, its a bad idea to use a user editable value for the primary key.
But, you have given me a list of someting to read. thanks.
not many regards
Edvard Korsbæk
December 3, 2014 at 8:17 am
CELKO (12/2/2014)
It has nothing to do with the physical storage like your silly IDENTITY. A file needs a unique physical storage locator, like a record number.
In SQL Server, an IDENTITY column is frequently used as a clustered index to control many physical aspects of the underlying file(s). It can prevent massive page splits (which can cause a huge waste of space and major slowdowns when range criteria is used) and, in tables where any natural key is usually a silly attempt (such as a Customer table), it allows for the clustering "key" to meet all the requirements of being unique, narrow, ever increasing, and unchanging. You have to remember that in SQL Server, ALL of the columns of the clustered index will be added to the key columns of ALL non-clustered indexes. Yes, Oracle (for example) works differently than SQL Server. Not every "best practice" works well on every database engine at the file level.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply