Solved

Convert Error

Posted on 2008-10-24
7
272 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

738 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