ITKnowledge Exchange Memb

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


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)
cast(month(getdate())as varchar)
end + 
case len(cast(day(getdate())as varchar))
when 1 then
'0' + cast(day(getdate())as varchar)
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.

This was first published in March 2006

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: