The following question and answer appeared in our ITKnowledge Exchange discussion forums: Return only the date part of a SQL smalldatetime field
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.
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)
This was first published in March 2006