how can the date time format can be resolved plese

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

SQL Server 2000 Forums

SQL Server Development (2000)

how can the date time format can be resolved plese

Author

Topic

abhishekvyas
Starting Member

2 Posts

Posted - 2007-07-27 : 06:22:09

SELECT DRDomain.name, DRLearningActivity.code, DRLearningActivity.title,DRLearningActivity.description,

CASE
WHEN DRLearningActivity.description IS NULL THEN 'NULL'
END as LAdesc,

DRLearningActivity.state,
DRLearningActivityTranscript.ID, DRUser.userID,

CASE
WHEN DRLearningActivityTranscript.enrollmentStatus = 'A' then 'Active'
WHEN DRLearningActivityTranscript.enrollmentStatus = 'R' then 'Retired'
WHEN DRLearningActivityTranscript.enrollmentStatus = 'U' then 'Unavailable'
WHEN DRLearningActivityTranscript.enrollmentStatus = 'C' then 'Completed'
END as enrollmentStatus,

DRLearningActivityTranscript.dateTimeEnrolled,
DRLearningActivityTranscript.dateTimeCompleted

FROM DRDomainMap INNER JOIN
DRDomain ON DRDomainMap.childID = DRDomain.ID INNER JOIN
DRDomainMembership ON DRDomain.ID = DRDomainMembership.domainID INNER JOIN
DRUser ON DRDomainMembership.entityID = DRUser.userID INNER JOIN
DRLearningActivityTranscript ON DRUser.userID = DRLearningActivityTranscript.userID INNER JOIN
DRLearningActivity ON DRLearningActivityTranscript.learningActivityID = DRLearningActivity.ID

WHERE (DRDomainMap.parentID = 15) AND (DRDomainMembership.entityTypeID = 1)
AND (DRDomainMembership.priority = 1)
AND (DRLearningActivityTranscript.enrollmentStatus = 'C')
AND (DRLearningActivityTranscript.dateTimeEnrolled > CONVERT(DATETIME, '2005-07-01'))
AND (DRLearningActivityTranscript.dateTimeEnrolled < CONVERT(DATETIME, '2007-07-01'))
ORDER BY DRDomain.name

abhishek vyas

Kristen
Test

22859 Posts

Posted - 2007-07-27 : 06:28:38

"how can the date time format can be resolved plese"

If you mean "presentation" then its best to do that in the front end.

If you are having trouble with:

CONVERT(DATETIME, '2005-07-01')

then you should use "yymmdd" format - i.e. NO hyphens :

CONVERT(DATETIME, '20050701')

as IME the yyyy-mm-dd style will fail on certain server locales.

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-27 : 07:33:30

SELECT DRDomain.name, DRLearningActivity.code, DRLearningActivity.title,DRLearningActivity.description,

CASE
WHEN DRLearningActivity.description IS NULL THEN 'NULL'
END as LAdesc,

SELECT DRDomain.name, DRLearningActivity.code, DRLearningActivity.title,
CASE
WHEN DRLearningActivity.description IS NULL THEN 'NULL'
ELSE DRLearningActivity.description
END as LAdesc,

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources