June 25, 2010 at 3:04 am
Hi All
I need some help please the DB holds the date like this 131402524
in another Col the time like this 202902272
I need to convet the values above in to col for date dd/mm/yyyy or yyyy/mm/dd not fussed either will do
and the time as a SQL time format 14:45:45 ect
I have tried a few cast and convert but the value still looks like this 131402524
Many thanks in advance
Below is someting i found on the net it may help
date format is set to a varchar datatype that contains a numeric expression like this '131336971'. If you know what your looking for some developers will instantly realise what this is, what is it? I hear you ask, well if you convert this to Hexidecimal it becomes apparent that it is a 32-bit Integer representation of a date.
Lets step through the convertion;
Using Calc.exe in windows (or some other decimal to hex converter) paste the value into the calculator as decimal
Switch to Hex, to see a hexidecimal representation of the value.
Here is an example using the value mentioned earlier
131336971
After conversion to Hexidecimal it becomes;
7D40B0B
Breaking this Hex value down gives us the following Integer values;
7D4 = 2004
0B = 11
0B = 11
This means you would end up with the date "11/11/2004" (DD/MM/CCYY)
The same applies for time;
136780319
After conversion to Hexidecimal it becomes;
8271A1F
Breaking this Hex value down gives us the following Integer values;
8 = 8
27 = 39
1A = 26
This means you would end up with the time "8:39:26" (HH:MM:SS).
June 25, 2010 at 3:07 am
I think you will find and answer here:
http://www.sqlservercentral.com/Forums/FindPost941588.aspx
-- Gianluca Sartori
June 25, 2010 at 3:40 am
CREATE FUNCTION dbo.IntToDate
(
@Input INTEGER
)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
SELECT value = DATEADD(DAY, (@Input & 0x000000FF) - 1, DATEADD(MONTH, (@Input & 0x0000FF00) / 256 - 1, DATEADD(YEAR, (@Input & 0xFFFF0000) / 65536 - 1900, 0)));
GO
SELECT is_deterministic = OBJECTPROPERTYEX(OBJECT_ID(N'dbo.IntToDate', N'IF'), 'IsDeterministic'),
is_precise = OBJECTPROPERTYEX(OBJECT_ID(N'dbo.IntToDate', N'IF'), 'IsPrecise'),
is_system_verified = OBJECTPROPERTYEX(OBJECT_ID(N'dbo.IntToDate', N'IF'), 'IsSystemVerified');
GO
DECLARE @Sample TABLE (data INTEGER NOT NULL);
INSERT @Sample (data) VALUES (131402524);
INSERT @Sample (data) VALUES (131336971);
SELECT S.data, iTVF.value
FROM @Sample S
CROSS
APPLY dbo.IntToDate(S.data) iTVF;
SELECT *
FROM dbo.IntToDate(131402524);
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 25, 2010 at 3:54 am
Great code, Paul, as usual!!
Just one question: what does schemabinding mean here? The return value depends only on the input parameter, so I miss the point on enabling shemabinding.
-- Gianluca Sartori
June 25, 2010 at 4:01 am
Gianluca Sartori (6/25/2010)
Great code, Paul, as usual!!
Thanks. Sorry I didn't notice your reply before.
Just one question: what does schemabinding mean here? The return value depends only on the input parameter, so I miss the point on enabling shemabinding.
SCHEMABINDING ensures that SQL Server checks the function for determinism.
A function that is not schema-bound is marked by SQL Server as non-deterministic and as doing data access.
http://blogs.msdn.com/b/sqlprogrammability/archive/2006/05/12/596424.aspx
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 25, 2010 at 4:18 am
I didn't know that. Thank you for pointing it out.
I think I'll be adding schemabinding to LOTS of UDF soon. 😉
-- Gianluca Sartori
June 25, 2010 at 4:39 am
Gianluca Sartori (6/25/2010)
I didn't know that. Thank you for pointing it out.I think I'll be adding schemabinding to LOTS of UDF soon. 😉
You're welcome. Be sure to test before making those changes - there can be side-effects.
The optimiser can perform many more tricks on deterministic functions. These extra tricks will usually benefit the plan chosen, but it is not impossible for it to produce a less optimal plan.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 25, 2010 at 5:16 am
Paul White NZ (6/25/2010)
...SCHEMABINDING ensures that SQL Server checks the function for determinism.
A function that is not schema-bound is marked by SQL Server as non-deterministic and as doing data access.
http://blogs.msdn.com/b/sqlprogrammability/archive/2006/05/12/596424.aspx
...
Paul, it is quite strange that there aren't many SQL developers aware of it and most of examples of writting UDFs do not mention this at all. I am surprised to see the url to the above blog... It doesn't appear in first pages of google search results if searched for "deterministic" and "UDF"...
The most strange thing is: I have submitted the article into SQLServerCentral about this just Yesterday London evening time :-D.
Might be I am wrong? Is it common knowledge?
Cheers,
Eugene
June 25, 2010 at 6:56 am
Eugene Elutin (6/25/2010)
Paul, it is quite strange that there aren't many SQL developers aware of it and most of examples of writting UDFs do not mention this at all. I am surprised to see the url to the above blog... It doesn't appear in first pages of google search results if searched for "deterministic" and "UDF"...
Try a search for "schemabinding udf" - it's result #4 🙂
The most strange thing is: I have submitted the article into SQLServerCentral about this just Yesterday London evening time :-D. Might be I am wrong? Is it common knowledge?
It's not common knowledge, no. A number of articles have been written on the subject, so I'm not at all sure *why* more people don't know about it.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 25, 2010 at 7:02 am
Paul White NZ (6/25/2010)
It's not common knowledge, no. A number of articles have been written on the subject, so I'm not at all sure *why* more people don't know about it.
I can speak for myself: I never read an article on the subject.
To arrive at reading an article you have to look for it, if it doesn't come with a newsletter or print.
If you don't run into problems, you never ask yourself some particular questions, so you don't look for resources.
-- Gianluca Sartori
June 25, 2010 at 7:10 am
Gianluca Sartori (6/25/2010)
I can speak for myself: I never read an article on the subject. To arrive at reading an article you have to look for it, if it doesn't come with a newsletter or print. If you don't run into problems, you never ask yourself some particular questions, so you don't look for resources.
That's a good point.
I first came across this around the time the article I referenced came out - which would be four or so years ago. I was aware that the optimiser cannot apply many exploration or transformation rules when it encounters a non-deterministic function, so I checked my functions for determinism using OBJECTPROPERTY. I was surprised to find that a simple scalar function that just multiplied an integer by one, and did no data access, was marked as doing data access and as not being deterministic. I found that SCHEMABINDING solved the issue, but it wasn't until I read that blog entry that I understood why.
I have banged on about binding functions for as long as I have been posting on here, but I guess not everyone in the world reads my posts and runs my code :laugh: :laugh: :laugh:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 25, 2010 at 7:17 am
Paul White NZ (6/25/2010)
... but I guess not everyone in the world reads my posts and runs my code
They don't know what they're missing! 😛
-- Gianluca Sartori
June 25, 2010 at 7:30 am
Paul White NZ (6/25/2010)
...Try a search for "schemabinding udf" - it's result #4
...
Cool, it works. But...
To search for the above word combination you probably need to know about schemabinding in the first place...:-)
I would think that the person who is trying to write udf would search for "write deterministic udf", or "deterministic udf" or "make udf deterministic". Unfortunately, these searches do not return much relevant or clear details about importance of specifying schemabinding option...
MSDN T-SQL reference for CREATE FUNCTION doesn't mention it...
Actually, I managed to find MSDN article http://msdn.microsoft.com/en-us/library/aa214775(SQL.80).aspx
, but again, it was not in results of the above searches...
But of cause,
He who seeks will find...
😀
June 25, 2010 at 7:40 am
Paul White NZ (6/25/2010)
...I first came across this around the time the article I referenced came out - which would be four or so years ago. I was aware that the optimiser cannot apply many exploration or transformation rules when it encounters a non-deterministic function, so I checked my functions for determinism using OBJECTPROPERTY. I was surprised to find that a simple scalar function that just multiplied an integer by one, and did no data access, was marked as doing data access and as not being deterministic. I found that SCHEMABINDING solved the issue, but it wasn't until I read that blog entry that I understood why.
...
It was about exactly the same for myself. SQL2000, simple scalar function, strange performance, checked OBJECTPROPERTY ( SELECT OBJECTPROPERTY(OBJECT_ID('f_GetValue'), 'IsDeterministic') ), surprised in result. That is when I found this: http://msdn.microsoft.com/en-us/library/aa214775(SQL.80).aspx...
June 25, 2010 at 7:41 am
Paul, that's great! I was hoping that when the other thread about this came up one of you gurus would come in and shatter my little code. There's always more to learn with SQL, and posts like yours are why I signed up to SQLServerCentral in the first place.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply