Convert date format

10 pts.
Tags:
Date conversion
Date format
How do I convert the given number to date format e.g. 2009062 should result in 03-Mar-2009.

Answer Wiki

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

Making a few assumptions… That looks like you have a YYYYJJJ date, where the rightmost 3 characters are the Julian day of year. In SQL Server, there are no built-in Julian date functions, so you need to massage it via code. One possibility:

create function fyyyyjjj2datestring (
@juliandate varchar(32)
)
return varchar(32)
begin
declare @year varchar(4)
declare @jjj varchar(3)
declare @date datetime
declare @outstring varchar(32)

set @year = left(@juliandate, 4) — extract the year part for later
set @jjj = right(@juliandate, 3) — extract the julian day part for later

set @date = convert(datetime, ’01-01-‘ + @year) — generate the date value for 1/1/yyyy
set @date = dateadd(d, convert(int, @jjj)-1, @date) — offset to the julian day (note the “-1″!)
set @outstring = replace(convert(varchar(32), @date, 106), ‘ ‘, ‘-‘) — convert to a formatted date result
— note that there is no defined format with “dd-MON-yyyy”, so I used style 106
— which is “dd MON yyyy” and then replaced the spaces with hyphens.

return @outstring

end

As expected, this function returns the string “03-MAR-2009″ for “2009062” as the input.

If your input is numeric instead of a string, then the 1st 2 “sets” would be:

set @year = convert(varchar(4), @juliandate / 1000)
set @jjj = @juliandate mod 1000 — also, declare @jjj as int and eliminate the convert in the dateadd

Note the “-1″ in the dateadd expression. Since you cannot define a date as “january 0, yyyy”, the JJJ offset needs to be bumped down (2009001 = 1/1/2009 = 01-01-2009 + 1 – 1).

Discuss This Question: 1  Reply

 
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
  • BrentSheets
    Moderator Note: Hello SanikaGurav and welcome to IT Knowledge Exchange. When you post a question, please include enough details for other members. Questions should include the platform, software, version, etc. You can provide more information or engage other members on a specific question by clicking the "Add to Discussion" button to add a comment. Thanks!
    6,925 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