float to date

Question

Post reply

float to date

joanna.seldon

Ten Centuries

Points: 1188
More actions
May 23, 2017 at 5:13 am

#337947

Hi
I am trying to convert a "calendar date" column that is a float to date
the "date" looks like 201601 should be 01-2016 or year 2016, month 01
I have tried to use date part and cast with no joy
please help

Viewing 12 posts - 1 through 11 (of 11 total)

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

tripleAxe SSCertifiable Points: 5706 More actions · Answer 1

What does your float look like?

I normally use CONVERT to get a datetime value e.g.

SELECT CONVERT(DATETIME,42811.5145744599)

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 2

deleted ....code window failing

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

DECLARE @d FLOAT= 201601; SELECT CONVERT( DATE, CONVERT(VARCHAR(8), CONVERT(INT, @d * 100 + 1)));

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Or this:
DECLARE @date float = 201601; SELECT DATEFROMPARTS(CAST(@date/100 AS int),CAST(@date AS int)%100,1)

John

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 5

Chipping in, float and datetime are compatible, in fact the conversion between the two is one of the fastest date/time operations in SQL Server
😎

USE TEEST; GO SET NOCOUNT ON;

DECLARE @FDATE FLOAT = CONVERT(FLOAT,GETDATE(),0); DECLARE @TDATE DATE = CONVERT(DATE,CONVERT(DATETIME,(FLOOR(@FDATE)),0),0);

SELECT @TDATE AS RESULT_DATE;

joanna.seldon Ten Centuries Points: 1188 More actions · Answer 6

joanna.seldon

Ten Centuries

Points: 1188

May 23, 2017 at 8:03 am

#1943793

Hi

thanks team , amazing !

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 7

J Livingston SQL - Tuesday, May 23, 2017 5:30 AM
DECLARE @d FLOAT= 201601; SELECT CONVERT( DATE, CONVERT(VARCHAR(8), CONVERT(INT, @d * 100 + 1)));

Careful here, add the format parameter to the convert, otherwise it might fail
😎

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 8

Eirikur Eiriksson - Tuesday, May 23, 2017 8:27 AM
J Livingston SQL - Tuesday, May 23, 2017 5:30 AM
DECLARE @d FLOAT= 201601; SELECT CONVERT( DATE, CONVERT(VARCHAR(8), CONVERT(INT, @d * 100 + 1)));
Careful here, add the format parameter to the convert, otherwise it might fail
😎

would this be better Eirikur?
DECLARE @d FLOAT= 201613; SELECT TRY_CONVERT( DATE, CONVERT(VARCHAR(8), CONVERT(INT, @d * 100 + 1)));

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 9

Eirikur Eiriksson - Tuesday, May 23, 2017 6:29 AM
Chipping in, float and datetime are compatible, in fact the conversion between the two is one of the fastest date/time operations in SQL Server
😎
USE TEEST; GO SET NOCOUNT ON;
DECLARE @FDATE FLOAT = CONVERT(FLOAT,GETDATE(),0); DECLARE @TDATE DATE = CONVERT(DATE,CONVERT(DATETIME,(FLOOR(@FDATE)),0),0);
SELECT @TDATE AS RESULT_DATE;

+1 to THAT! Conversion to Float and using Floor is actually faster than the DATEADD/DATEDIFF trick for "dropping time" from dates (among other things). I don't post it because it's not worth the argument with all the "Never do direct date math" purists out there.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 10

J Livingston SQL - Tuesday, May 23, 2017 8:37 AM
Eirikur Eiriksson - Tuesday, May 23, 2017 8:27 AM
J Livingston SQL - Tuesday, May 23, 2017 5:30 AM
DECLARE @d FLOAT= 201601; SELECT CONVERT( DATE, CONVERT(VARCHAR(8), CONVERT(INT, @d * 100 + 1)));
Careful here, add the format parameter to the convert, otherwise it might fail
😎
would this be better Eirikur?
DECLARE @d FLOAT= 201613; SELECT TRY_CONVERT( DATE, CONVERT(VARCHAR(8), CONVERT(INT, @d * 100 + 1)));

Skipping the character conversion and convert straight to datetime is safer, when I change the collation/locale then your query either errors or returns NULL
😎

DECLARE @d FLOAT= CONVERT(FLOAT,GETDATE(),0); SELECT CONVERT( DATE, CONVERT(VARCHAR(20), CONVERT(DATETIME, @d,12),12),12);

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 11

Jeff Moden - Tuesday, May 23, 2017 8:44 AM
Eirikur Eiriksson - Tuesday, May 23, 2017 6:29 AM
Chipping in, float and datetime are compatible, in fact the conversion between the two is one of the fastest date/time operations in SQL Server
😎
USE TEEST; GO SET NOCOUNT ON;
DECLARE @FDATE FLOAT = CONVERT(FLOAT,GETDATE(),0); DECLARE @TDATE DATE = CONVERT(DATE,CONVERT(DATETIME,(FLOOR(@FDATE)),0),0);
SELECT @TDATE AS RESULT_DATE;
+1 to THAT! Conversion to Float and using Floor is actually faster than the DATEADD/DATEDIFF trick for "dropping time" from dates (among other things). I don't post it because it's not worth the argument with all the "Never do direct date math" purists out there.

Thanks Jeff, will try to find the last test harness I did on this, IIRC it's around 70% faster than using the DATEXX functions
😎