harri.reddy (10/2/2012)

---

/*** PROBLEM #1 ***/

--Correct the following query:

SELECT*

FROMDATA

WHERECAST(VAL AS DATETIME) BETWEEN '12/1/2012' AND '12/31/2012'

AND ISDATE(VAL) = 1

/*** PROBLEM #2 ***/

--Write a set of queries that outputs all the non-date values...

Impossible to know without seeing the data, but I'd guess that the field isn't a datetime datatype, but rather a varchar() where not all of the values are correctly formatted...

See if this helps: http://technet.microsoft.com/en-us/library/ee210531.aspx

HTH,

Rob

deepeshdhake (10/2/2012)

---

I have the date time field. I want extract only Month and the year. How can I do the same.

Current format of the field is "10/01/2012 hh:mm:ss"

Need to convert...

Take a look at the FOR XML clause.

http://msdn.microsoft.com/en-us/library/ms190922%28v=sql.105%29.aspx

HTH,

Rob

Laurie, Yep -- very similar to what I did end up doing upstream in the thread.

Thanks,

Rob

ERWin is one tool that can be used for modeling, reverse enigineer schema, forward engineer schema (make change scripts). It doesn't integrate into VS (that I'm aware of). ...

Steve, that doesn't work as every class in the Curriculum table has been taken by at least one user. So the inner SELECT of:

select *

from LMS.Curriculum c1

left outer join

LMS.UserCurriculum...

Dan,...

Thank you for breaking that down for me. No users table, but I can do:

;WITH CTE_Users (Username)

AS

(

SELECT DISTINCT Username

FROM LMS.UserCurriculum

)

SELECT c.*,

u.*,

uc.*

FROM...

Okay, but if I switch to a right join I still get the total number of rows from the UserCurriculum table.

SELECT c.*,

uc.*

FROM LMS.Curriculum c

RIGHT...

jampabsatish (10/1/2012)

---

Hi Forum,

I need to generate a sequence number using SSIS , this value is stored in the column as a varchar(15),

001-00-000001

001-00-000002

001-00-000003

It will NEVER reach the value 001-00-999999

I'm trying to...

Yes, I can have one particular test user that has taken 9 of the 15 classes from the Curriculum table.

After last night's reboot of the server, the problem has went away and the DBCC comes back fine.

Rob

Books onine says:

The hierarchyid data type is a variable length, system data type. Use hierarchyid to represent position in a hierarchy. A column of type hierarchyid does not automatically represent...