Converting Float to Datetime and comparing

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Converting Float to Datetime and comparing

Author

Topic

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-07-24 : 03:42:21

I have a field (fld1) that I need to compare between with another for dates, the field is coming into the database from an external source as a float in the format CCYYMMDD. The field (fld2) I want to compare it with is also a float but in the format YYMMDD.

What's the best way of converting and comparing?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-24 : 03:51:53

If you handle dates, you should always use proper datetype; DATETIME or SMALLDATETIME.
If you do not want to break the existing application, you can make a calculated column.
Or a convert on the fly, like this

DECLARE	@fld1 FLOAT,
	@fld2 FLOAT

SELECT	@fld1 = 19950822,
	@fld2 = 941231

SELECT	CONVERT(DATETIME, STR(@fld1, 8, 0)),
	CONVERT(DATETIME, STR(@fld2, 6, 0))

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-24 : 03:52:11

Why are you using float fields for storing dates?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-24 : 03:53:26

I think it's out of his control.
He wrote the data come from an external source. He is just doing his best to solve this issue.

E 12°55'05.25"
N 56°04'39.16"

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-07-24 : 03:58:05

Thanks both, Yes the data comes from outside and I've got to compare it as if it were dates (a pain) so I wanted to know whats the best way to do it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-24 : 04:00:52

There are multiple ways to handle this.
This is another way

DECLARE	@fld1 FLOAT,
	@fld2 FLOAT

SELECT	@fld1 = 19950822,
	@fld2 = 941231

SELECT	CAST(@fld1 AS INT) % 1000000,
	CAST(@fld2 AS INT)

You have to test different solution so see what fit your environment the best.
Different DBMS has different strengths.

E 12°55'05.25"
N 56°04'39.16"

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-07-24 : 04:03:50

We have sql server 2005 on a windows server 2003 if that makes any difference?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-24 : 04:05:44

And the source come from ... where ?
Well, it really doesn't matter now.

Now is the time for you to do some tests, right?

E 12°55'05.25"
N 56°04'39.16"

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-07-24 : 04:09:18

I've only just started here, but I think it comes in as a csv and is imported into a holding table in our sql server

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-07-24 : 04:10:53

Yes, your right I'll have to test both methods and see what's best, Thanks again for your help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-24 : 04:12:15

CSV-files has no datatypes.

How is the file imported?
What datatypes do fld1 and fld2 have in the staging [holding] table?

You can [maybe] make a difference directly and change the two columns (fld1 and fld2) to datetime and [maybe] the import will convert the values of the two columns automatically for you?

E 12°55'05.25"
N 56°04'39.16"

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-07-24 : 04:37:58

I'll look into doing that (if I can get access to it) and that may be a simpler solution, thanks!

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-07-24 : 08:11:00

chk this...

declare
@t datetime,
@t1 datetime

set @t='19950822'
set @t1='941231'

SELECT CONVERT(VARCHAR(26),@t, 110)
SELECT CONVERT(VARCHAR(26),@t1, 110)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources