Extremely New - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Extremely New

Author

Topic

robone
Starting Member

5 Posts

Posted - 2007-01-26 : 06:25:46

Hi,

I am very new to SQL, and am basically self-taught(apart from SQL for Dummies and a couple of other books). I am hoping someone can help me with the 'CONVERT' statement.

My system outputs the date format as '12345'. What I have written so far is this;

select Resprj.Re_code, Res.re_desc, resprj.Pr_code, projects.Pr_desc,Res.Re_status1,
Projects.active, Projects.Pr_start, Projects.Pr_end
from res inner join Resprj on (Res.Re_code = resprj.Re_code)
inner join projects on (projects.PR_code = resprj.Pr_code)
and Projects.Pr_desc like '%C9%'
where projects.active =-1
order by Projects.Pr_code, Res.Re_desc

Could someone please help in regards to using the 'CONVERT' statement to change the date from '12345' to dd/mm/yy.

Thanks

Rob

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-26 : 06:31:04

what date does 12345 represent ?

robone
Starting Member

5 Posts

Posted - 2007-01-26 : 08:31:01

Hi Sorry,

This is the date format that our databases seems to output at. It does the same in Access. Its just a 5 figure date format for example 39108 is todays date. I have to convert the date format via Excel, by copying and pasting and then using format to change the numbers to date format.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-26 : 08:56:10

try dateadd(day, 0, 12345)

or dateadd(day, 0, yourdatecol)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-26 : 09:40:47

That may be an Excel date. I believe Excel starts with day 0 as 1899-12-31, but there is also a bug/feature in Excel leap year logic that sees the year 1900 as a leap year, so you have to subtract one more date and start with 1899-12-30.

Try this code