It goes LIKE this:

SELECT

StudentZip,

SUBSTRING(StudentZip, 1, 5) AS CleanedZip

FROM DimStudents

WHERE (StudentZip LIKE '[0-9][0-9][0-9][0-9][0-9]%')

Yulichka,

It depends on exactly what you're trying to do, which you havent told us, but if you want the behaviour of the SQL Server expression to be as close as...

Glad to help, but I'm not as good as whoever thought up that ROW_NUMBER difference trick in the first place.

--Andrew

This method used to solve this problem of combining sequential rows originated from the following article:

http://www.sqlmag.com/Article/ArticleID/93606/sql_server_93606.html

DECLARE @data TABLE (

timecode char(1),

datestart datetime,

dateend datetime,

totalweight int

)

/* Insert test...

Hi Jeff,

Yes but I got UTC and local time corrections back to front first time around.

If all you need is...

I think the PIVOT query below does what you're looking for.

DECLARE @Employee TABLE (

Emp_Id INT NOT NULL,

Att VARCHAR(10) NOT NULL, -- Present/absent/Leave....

date datetime

)

/* Add some test data: previous 365 days...

Does this query give you what you want?

For each month, it returns the row with the most recent MYDATE value for that month, which is not necessarily the last day...

You can build a temporary mapping table and perform all the updates in a single UPDATE statement - something like the following.

/* Create mapping temporary table */

CREATE TABLE #ExtCityMap (

Extension...

This script will return the Nth day of a specified weekday in any target month. It's not convenient to use if you to need to embed it directly in a...

I did a DATEADD function to add 86399 seconds to the dates, making them the equivolent of 23:59:59, since that seemed to me to be the easiest way to calculate...

dphillips,

Yes, you are probably right that using CHARINDEX alone may be a more exact translation of the Access behaviour. It's a while since I used Access so I can't remember...

If you use the SUBSTRING function, just set the 3rd argument of the SUBSTRING function to be the same as the maximum number of characters that the [Field6] column can...

The direct translation is:

RIGHT([Field6], LEN([Field6]) - CHARINDEX('Unit', [Field6]) + 1)

However, the the following is likely to be slightly more efficient.

Just change the final argument of the SUBSTRING function to...

Try casting salary and/or age to datatypes large enough to store the total sum of these columns. You could use bigint, float or the decimal data types. It's probably the...