how convert float to nvarchar

Question

Post reply

how convert float to nvarchar

tengtium

SSC-Addicted

Points: 457
More actions
September 6, 2006 at 7:27 pm

#367312

for example:
SELECT CAST(CAST(getdate() AS datetime) AS float)
how can i convert the return select value to nvarchar????

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

getdate() IS datetime by definition. So, you don't need to cast it as datetime.

To get varchar use this:

SELECT STR(CAST(getdate() AS float), 12, 4)

_____________
Code for TallyGenerator

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 2

If you want Date Time to be converted to Char then u need to use Convert function cos I don't know why you would want to convert date to float and then to Char.

Thanks

Sreejith

tengtium SSC-Addicted Points: 457 More actions · Answer 3

-- this statement represent the float of the current date

SELECT CAST(CAST(getdate() AS datetime) AS float)

-- return to original date format

SELECT CAST((SELECT CAST(CAST(getdate() AS datetime) AS float)) AS datetime)

-- the reason is i want to use the floating point value to be my unique key in the format of nvarchar

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

the reason is i want to use the floating point value to be my unique key in the format of nvarchar

Why? If you want to use the current date as the unique key (which is risky if you have frequent inserts) then just use getdate. Why would you want to store a float as an nvarchar?

Be careful converting datetimes to float and back, since floats aren't exact, you can loose precision casting a date to float and back. It's a very quick way to strip off the time (cast to float, floor and cast back to datetime) but otherwise you can introduce inaccuracies.

I ran the following

SELECT getdate(), CAST(CAST(getdate() AS float) AS datetime)

and got back 2006-09-07 11:17:08.473 and 2006-09-07 11:17:08.470

Also, as mentioned before, you don't need to cast getdate to datetime, it's already a date time.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

tengtium SSC-Addicted Points: 457 More actions · Answer 5

so it's not possible to convert a floating point value to nvarchar????

Gopi Muluka SSCarpal Tunnel Points: 4594 More actions · Answer 6

I think you concluded in a wrong way

It is possible to convert float datatype to nvarchar datatype

Sergiy SSC Guru Points: 110208 More actions · Answer 7

Did you read every post in this topic?

_____________
Code for TallyGenerator

tengtium SSC-Addicted Points: 457 More actions · Answer 8

declare @current_date datetime

declare @float_representation float

set @current_date = getdate() -- assuming this will return this value: 2006-09-08 10:01:03.437

-- now convert to float using cast

declare @character_representation nvarchar(100)

set @character_representation = CAST(@current_date AS float) -- this will return this value : 38966.419371064818

-- and store it to variable

print @character_representation -- print 38966.4

-- my problem is i'm losing the precision.... or the number of character(numbers) of the float value...

-- my question is" Is it possible not losing the number of character(numbers) of the float value...

Sergiy SSC Guru Points: 110208 More actions · Answer 9

Did you notice function STR in my post?

Did you bother to open BOL and read couple of words about this function?

declare @F float

SET @F = 38966.419371064818

select STR(@F, 18, 12)

Result:

38966.419371064818

_____________
Code for TallyGenerator