return only the date part of a sql smalldatetime field

pts.
Tags:
SQL Server
Please help, 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 this 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. Ciao

Answer Wiki

Thanks. We'll let you know when a new response is added.

bjack8468,

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)

I hope this helps.

Charles

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

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
  • Bjack8468
    Charles you are awesome. Thank you soooo much. Good enough for me. This is gonna fly as is.
    0 pointsBadges:
    report
  • Jaylou
    try this: select * from msdb..sysjobhistory where run_date = CONVERT(CHAR(8),getdate(), 112) this will get you want you asked for. below is 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)
    0 pointsBadges:
    report
  • KingConan
    There is an easier way. The DATEPART function already returns an integer. Use SELECT DATEPART(month, getdate()). See article: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_2mic.asp Kevin
    0 pointsBadges:
    report

Forgot Password

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

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

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

Thanks! We'll email you when relevant content is added and updated.

Following