Return only the date from a SQL smalldatetime field

The following question and answer appeared in our ITKnowledge Exchange discussion forums: Return only the date part of a SQL smalldatetime field

Question

Member "bjack8468" writes: Does anyone know how to return only the date part of the SQL datetime field? Or how to convert an integer field to a date field? For example, I need to convert integer 20060223 to a date field. The sysjobhistory table stores the run_date as an integer and I need to query sysjobhistory for run_date = getdate(), but because the type is different, they cannot be compared. I'd really appreciate any feedback you may have.

Answers

Member "CharlesJC" writes: There may be a more efficient way to do this, like making a function that converts the date to an integer, but you could compare run_date to

select cast(
cast(year(getdate()) as varchar) + 
case len(cast(month(getdate())as varchar))
when 1 then
'0' + cast(month(getdate())as varchar)
else
cast(month(getdate())as varchar)
end + 
case len(cast(day(getdate())as varchar))
when 1 then
'0' + cast(day(getdate())as varchar)
else
cast(day(getdate())as varchar)
end as int)

Member "jaylou" writes: Try this:

select * from msdb..sysjobhistory where run_date = CONVERT(CHAR(8),getdate(), 112)

This will get you want you asked for.

Below are more samples to change around dates. (Notice I placed the date in ticks. You get an arithmatic overflow if you leave the int as an int.)

declare @date datetime
select @date = '20060223'
select @date 
-- this will give you only the date part of @date
select convert(varchar(12),getdate(),101)

Click to read the full discussion and additional responses to this question.