CODEWITHSUNDEEP
sql-servert-sql
Roger Dodger
Roger Dodger

Reputation: 987

T-SQL : separating datetime into date and time and then converting to numeric

My requirement is much more specific than what I found - I have a table that stores a datetime value. I need to retrieve that value, separate it into a date and a time, and then convert both those values into integer values. This is the field I'm trying to retrieve:

release_date = 2016-06-28 07:04:17.960

This needs to be split like so:

--numeric date as yyyymmdd from above value would be:
@my_numeric_date = 20160628

--numeric time as hhmmss from above value would be:
@my_numeric_time = 70417

Is there a relatively straightforward way of achieving this?

Upvotes: 0

Views: 118

Answers (2)

R. Horber
R. Horber

Reputation: 510

The last time I needed this, I used these two statements:

CONVERT(INTEGER, CONVERT(VARCHAR, release_date, 112)),
CONVERT(INTEGER, REPLACE(CONVERT(VARCHAR, release_date, 108), ':', ''))

So, to save it in variables use:

SELECT 
@my_numeric_date = CONVERT(INTEGER, CONVERT(VARCHAR, release_date, 112)),
@my_numeric_time = CONVERT(INTEGER, REPLACE(CONVERT(VARCHAR, release_date, 108), ':', ''))

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81960

Declare @Date DateTime = GetDate()

Select DateInt = (Year(@Date)*10000)+(Month(@Date)*100)+Day(@Date)
      ,TimeInt = (DatePart(HH,@Date)*10000)+(DatePart(MINUTE,@Date)*100)+DatePart(SECOND,@Date)

Returns

DateInt     TimeInt
20160628    104510

-- The DateTime was 2016-06-28 10:45:10.017

Upvotes: 1

Related Questions