Convert smalldatetime to int

Question

Post reply

Convert smalldatetime to int

JeremyE

SSCoach

Points: 16213
More actions
July 17, 2007 at 5:16 pm

#117943

How can I go about converting smalldatetime into int(4)?

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

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

Sergiy SSC Guru Points: 110208 More actions · Answer 1

First you need to define the rule for your conversion.

07/17/2007 5:16:00 PM must become - what?

07/17/2007 8:20:00 PM must become - what?

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 2

SELECT CAST(CONVERT(CHAR(8),somedate,112) AS INT)

...but it's an insane thing to do for so many reasons...

--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)

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 3

... and, that's just the date... doesn't include time...

But, Serqiy is correct... there's a date format that's based on the number of seconds since the 01/01/1970. There are other "numeric" date formats such as Microsofts DateSerial number... we need to know what the format is that you want. Answering Serqiy's questions would be a great start

--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)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

If the integer you want to convert to is UNIX time, this will do it:

select datediff(ss,'19700101',MySmallDateTime)

The functions in this script can be used to convert to/from SQL Server date time to UNIX Time.

UNIX Time Conversion Functions:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=66858

Sergiy SSC Guru Points: 110208 More actions · Answer 5

And if it suppose to be Excel days then ...

And if it suppose to be ...

So, question remains: what it suppose to be?

_____________
Code for TallyGenerator

JeremyE SSCoach Points: 16213 More actions · Answer 6

After getting a little more info myself, it's supposed to be Unix time. The number of seconds since midnight UTC of January 1, 1970. Michael's solution works perfectly. Thanks.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 7

Good guess on my part, huh?

My experience is that posts like this are almost always looking for a conversion to/from UNIX time, so I took a shot at it.

noeld SSC Guru Points: 96590 More actions · Answer 8

Good guess? That was pure magic guess!!!! ...

* Noel

polkadot SSChampion Points: 13664 More actions · Answer 9

I've mistakenly created a column as smalldatetime instead of smallint and have imported data into that table. The values in that column are all 1900-01-01 00:00:00.000 or 1900-01-04 00:00:00 or 1900-01-03 00:00:00.

I have not found a successful way to convert smalldatetime to smallint, in such a way that it recovers original values.

For example 1900-01-01 00:00:00.000 should really just be 1 and 1900-01-03 00:00:00 should just be 3.

At this point should I just create a new column with smallint datatype, use case statement to populate it, delete smalldatetime column, rename new column to original name?

--Quote me

Sergiy SSC Guru Points: 110208 More actions · Answer 10

DATEDIFF(dd, 0, [smalldatetime column]) + 1

_____________
Code for TallyGenerator