Thursday, May 11, 2017

Format Date with DATEPART

This is helpful when you need to get rid of milliseconds in a date for sorting.

DATETIMEFROMPARTS ( year(s.LastRunTime), month(s.LastRunTime), day(s.LastRunTime), DATEPART(hh,s.LastRunTime), DATEPART(mi,s.LastRunTime), 0, 0)
Posted by at No comments:

Monday, May 1, 2017

List of Date Formats

The website below has a list of SQL Server Date Formats.

 http://www.sql-server-helper.com/tips/date-formats.aspx
Posted by at No comments:

Subquery Join with Max Date

The query below is used when you have a table with multiple rows per person and you want to select the MAX Date.  First you will select the fields you want to display in the query, then you will create a subquery to select the Max Date, then you will join the query and subquery on the ID and the Max Date. 




SELECT ind.studentid

       ,Convert(Datetime,StatusEffectiveDate) AS EDStartDate

       ,economicdisadvantagecode

       ,Isnull(ind.economicdisadvantagecode + '-' + lku3.codedesc, '')  AS EconomicDisadvCode

INTO   #sle3

FROM   GetColumnsTable ind

       LEFT OUTER JOIN LookUpTable lku3

                    ON lku3.code = ind.economicdisadvantagecode

                       AND lku3.type = 'ECO'



----This Part grabs the Max Effective Date for the ID 

                       

Inner Join

(

       Select studentid, Max(Convert(Datetime,StatusEffectiveDate)) As StartDate

       From GetColumnsTable ind

       Group by StudentID

) ind2



----Here you join the first Query to the SubQuery and set your Criteria 



on ind.StudentID = ind2.StudentID

and Convert(Datetime,ind.StatusEffectiveDate) = Convert(Datetime,ind2.StartDate)

Posted by at No comments:
Subscribe to: Posts (Atom)