Solved

Convert Error

Posted on 2008-10-24
7
267 Views
Last Modified: 2012-05-05
     SELECT     Employee.FirstName , Employee.Dept ,
Emp_Time_Track.No_Of_Hrs_Spent , Emp_Time_Track.Status, Project.Project_Id, Project.Project_Nm ,
                     project.convert(varchar(8),start_dt,112), project.convert(varchar(8),start_dt,112) as end_dt, Project.Client_Nm , Task.Task_Nm ,
Task.No_Of_Res_Planned , Task.No_Of_Hrs_Planned , Task.Descr
FROM         Emp_Time_Track INNER JOIN
                      Employee ON Emp_Time_Track.Emp_Id = Employee.Emp_Id INNER JOIN
                      Project ON Emp_Time_Track.Project_Id = Project.Project_Id INNER JOIN
                      Task ON Emp_Time_Track.Task_Id = Task.Task_Id AND Project.Project_Id = Task.Project_Id
WHERE     (Employee.FirstName = 'sam') AND (Employee.Dept = 'sqldba') AND (Project.Project_Nm = 'adg') AND (Task.Task_Nm = 'coding') AND
                      (Project.Start_Dt >= @start_dt) AND (Project.End_Dt <= Project.End_Dt)


The above convert is throwing error.How do i correct the error.Pls assist
0
Comment
Question by:srirama_rao
  • 4
  • 2
7 Comments
 
LVL 14

Expert Comment

by:Binuth
ID: 22794452
try this
    SELECT     Employee.FirstName , Employee.Dept ,

Emp_Time_Track.No_Of_Hrs_Spent , Emp_Time_Track.Status, Project.Project_Id, Project.Project_Nm ,

                    convert(varchar(8), project.start_dt,112),convert(varchar(8), project.start_dt,112) as end_dt, Project.Client_Nm , Task.Task_Nm ,

Task.No_Of_Res_Planned , Task.No_Of_Hrs_Planned , Task.Descr

FROM         Emp_Time_Track INNER JOIN

                      Employee ON Emp_Time_Track.Emp_Id = Employee.Emp_Id INNER JOIN

                      Project ON Emp_Time_Track.Project_Id = Project.Project_Id INNER JOIN

                      Task ON Emp_Time_Track.Task_Id = Task.Task_Id AND Project.Project_Id = Task.Project_Id

WHERE     (Employee.FirstName = 'sam') AND (Employee.Dept = 'sqldba') AND (Project.Project_Nm = 'adg') AND (Task.Task_Nm = 'coding') AND

                      (Project.Start_Dt >= @start_dt) AND (Project.End_Dt <= Project.End_Dt)

Open in new window

0
 
LVL 14

Expert Comment

by:Binuth
ID: 22794458
I changed

project.convert(varchar(8),start_dt,112)

to

convert(varchar(8),project.start_dt,112)
0
 

Author Comment

by:srirama_rao
ID: 22794706
ur correct but i want the date in the format mm/dd/yyyy
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 14

Accepted Solution

by:
Binuth earned 500 total points
ID: 22794727
use

convert(varchar,project.start_dt,101)

instead of

convert(varchar(8),project.start_dt,112)
0
 

Author Comment

by:srirama_rao
ID: 22794971
If i put anything other than 101 my query is throwing error.If i put 112 my date format (output) is like 20081010,
so i wamted to but a  / or a - as a seperator.  That is why i am looking for a style
0
 
LVL 14

Expert Comment

by:Binuth
ID: 22795008
what error you getting ?
0
 
LVL 9

Expert Comment

by:jamesgu
ID: 22796126
did you changed project.convert To convert as Binuth said?
                           ^^^^^^^
0

Featured Post

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now