June 16, 2010 at 9:06 am
How can i convert decimal(18,0) to date time
ple help me .it bit urgent
June 16, 2010 at 9:19 am
Datetime is represented internally by a float value, where the integer part is the date value and the fractional part is the time value.
You could try casting with something like:
select cast(40344 as datetime)
Hope this helps
-- Gianluca Sartori
June 16, 2010 at 9:21 am
i tried that but my decimal date is in the format 131402252
June 16, 2010 at 9:28 am
So, it's not a valid date.
Probably that field has concatenated data inside. You will have to find out how data is represented in that column. Where does this data come from?
-- Gianluca Sartori
June 16, 2010 at 9:51 am
// This formula uses the two prompts 'From_Date' and 'To_Date' filled in by the user
// to control the date range for which the data is displayed in the report.
// Note : If you which to change the default dates which the 'From_Date' and the
// 'To_Date' prompts use, you must amend the prompts themselves aswell
// as the values in this formula.
DateVar DisplayDate := HelpDeskDateFieldToDate ({CALL_HDW.CALLDAT_HDW}); //The database field which is being displayed in the report
DateVar UsrFromDate := {?From Date}; //The 'From_Date' prompt filled in by the user
DateVar UsrToDate := {?To Date}; //The 'To_Date' prompt filled in by the user
DateVar DefaultFromDate := Date(0,0,0); //The default value for the 'From Date' prompt if the user does not enter their own value
DateVar DefaultToDate := Date(0,0,0); //The default value for the 'To Date' prompt if the user does not enter their own value
StringVar MinimumDate := "01/01/2001"; //The minimum date for records if no date is specified by the user
if((UsrFromDate = DefaultFromDate) and (UsrToDate <> DefaultToDate)) then // If the user has not entered a 'From_Date' value, only a 'To_Date' Value, then...
((DisplayDate >= StandardDateToCrystalDateFormat (MinimumDate)) and // The DataDisplayed will be from the first record to the user specified 'To_Date'
(DisplayDate <= (UsrToDate)))
else
if ((UsrFromDate <> DefaultFromDate) and (UsrToDate = DefaultToDate)) then // If the user has entered a 'From_Date' value, but not a 'To_Date' Value, then...
(DisplayDate >= (UsrFromDate)) // The DataDisplayed will be from the 'From_Date' specified by the user, upwards
else
if ((UsrFromDate = DefaultFromDate) and (UsrToDate = DefaultToDate)) then // If the user has entered neither a 'From_Date' value, nor a 'To_Date' Value, then...
(DisplayDate >= StandardDateToCrystalDateFormat (MinimumDate)) // The DataDisplayed will be from the first record (the date of which will be on or
// above the DefaultFromDate in the From_Date Prompt
else
if ((UsrFromDate <> DefaultFromDate) and (UsrToDate <> DefaultToDate)) then // If the user has entered both a 'From_Date' value, and a 'To_Date' Value, then...
((DisplayDate >= (UsrFromDate)) and // The DataDisplayed will be between the 'From_Date' and
(DisplayDate <= (UsrToDate))) // the 'ToDate' specified by the user.
June 16, 2010 at 9:58 am
131402252 decimal is changing to 02/09/2005 8:37:16
June 16, 2010 at 10:01 am
shanila_minnu (6/16/2010)
131402252 decimal is changing to 02/09/2005 8:37:16
I'm sorry, but I don't see how.
I don't understand that code. What language is it?
-- Gianluca Sartori
June 23, 2010 at 2:35 am
Hey, after recently answering this thread, I found the original one here so I thought I'd share how I solved it.
I've come across this before, but basically what you have there is a date stored in binary.
DECLARE @number AS BIGINT
SET @number = 131401986
I'll be using the above variable throughout this post, the one posted in the other thread. First, we need to cast the number as binary.
SELECT CAST(@number AS BINARY(8))
/*OUPUT
----------
0x0000000007D50902
*/
This binary number is split into three parts, the first two bytes represent the year.
SELECT CAST(0x0000000007D5 AS INT)
/*OUPUT
-----------
2005
*/
So, we can grab the year part with a cast of a substring of the number.
SELECT CAST(Substring(( CAST(@number AS BINARY(4)) ), 1, 2) AS INT)
From here it just becomes a case of knowing how the rest is laid out. I believe the next 1 byte is the month and the final 1 byte is the day.
SELECT CAST(Substring(( CAST(@number AS BINARY(4)) ), 1, 2) AS INT)
SELECT CAST(Substring(( CAST(@number AS BINARY(4)) ), 3, 1) AS INT)
SELECT CAST(Substring(( CAST(@number AS BINARY(4)) ), 4, 1) AS INT)
/*OUTPUT
-----------
2005
-----------
9
-----------
2
*/
So finally, we can use the following query to get convert it to datetime.
SELECT CAST(CAST(CAST(Substring(( CAST(@number AS BINARY(4)) ), 4, 1) AS INT) AS
VARCHAR) +
'/' + CAST(CAST(Substring(( CAST(@number AS BINARY(4)) )
, 3, 1)
AS
INT) AS VARCHAR) + '/' + CAST(
CAST(Substring((
CAST(
@number AS
BINARY
(4)) ), 1, 2
) AS
INT) AS VARCHAR) AS DATETIME)
This changes considerably when "time" is involved as well.
June 24, 2010 at 8:31 am
That code doesn't seem to produce the same results the OP is expecting, though.
June 24, 2010 at 8:57 am
I'll happily discuss that with the OP if he ever reports back, but I'm fairly certain that the error is in the expected result he posted here rather than in the code. For it to include "time", the binary number would've had to have been longer.
DECLARE @test-2 AS DATETIME
DECLARE @number AS BIGINT
DECLARE @binary AS BINARY
SET @test-2 = GetDate()
SELECT CAST(@test AS BINARY(8)) AS [Date+Time in Binary],
CAST(CAST(@test AS BINARY)AS BIGINT) AS [Date+Time as number]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply