SQL Help - Conversion of Float

Saviola · 16 Jun 2011 at 15:44

Howdi,

My brain is misfiring today and I cannot get this to work.

I need to convert a float (ie 20111010010101) into a a Date format I can use in SSRS (for date pickers etc) ie 01/01/2011 or 01-01-2011.

field name in SSRS if you want an to use it

= Fields!IS_RECEIVED_DATETIME.Value

For the life of me I cannot get it to work. Would like some fresh eyes if possible.

Cheers
Saviola

soullessfire · 16 Jun 2011 at 16:38

Can't you just use CONVERT and pass it a format?

CONVERT(datetime,<your value/variable here>, 103) AS FormattedDate

(103 being UK format with 4 digit years)

cotton31 · 16 Jun 2011 at 17:17

As above you're probably better off doing this in the data side rather than the layout.

You can just CAST it as well unless you want a specific date format (which I don't think you will for a date picker - is this for a default value for that?) i.e.

CAST(<yourfield> as datetime)

Saviola · 16 Jun 2011 at 17:23

Tried both ways but get invalid syntax all the time. I am doing it in the data and not layout, pasted the wrong variable above

table= ISSUES
field= IS_RECEIVED_DATETIME

I must be doing basic wrong as everything else has worked fine just the damn date conversion

soullessfire · 16 Jun 2011 at 17:39

Pick a date from your database and try putting it into those code samples posted here instead of your variable name.

SELECT CONVERT(datetime, '2011...........', 103) as mytestdate

or

SELECT CAST('2011......', datetime) as mytestdate

and see what it does with that. If that works, you might need to tell the database the format of that float (as in the number of digits before and after the decimal point). Only thing I can think of offhand.

soullessfire · 16 Jun 2011 at 17:49

Actually ignore my first example. the formatting applies to the variable not the result so that's totally wrong lol

The 103 should be a 0 for 6 digit float numbers, 1 for 8 digit float numbers and 2 for 16 digit.

cotton31 · 17 Jun 2011 at 09:49

Where are you getting your float date from? I'm not sure that's even a valid datecode.

I assume from your example above that the output should be 10/10/2011 01:01:01?

If I cast that date as a float, I get 40542.

You might end up joining each part of the number together to make a date.

Someone else might point out if I'm wrong because I'm not entirely sure!