As far as I can see the differences fall into two main areas, the plain part being described in the documentation: Weeks begin on Sundays, and week 1 begins with the first Sunday of the year. Days preceding the first Sunday of the year are in week 0. This behavior...

I recommend you generate the Date table from the script at Create and Populate at Date Dimension for Data Warehouse and use that to figure out when your next workday is. For my own purposes I dropped all the UK columns and Fiscal columns - though they maybe useful to...

There are two issues here, the first is that I suspect you are defining 8 weeks of data as having 8 different values for DATEPART(WEEK, in which case you can replicate the root cause of the issue by looking at what ISO would define as the first week of 2015:...

An easy way that works in almost any version of SQL Server is: select (case when month(@date) <> month(dateadd(day, 1, @date)) then 'true' else 'false' end) That is, add a day and see if you are in the same month. This works for leap years, and Dec 31 and so...

How about date_format()? select date_format(EntryDate, '%&Y_%m') This is the MySQL way. Your code looks like an attempt to do this in SQL Server. EDIT: The following should work in SQL Server: select DATENAME(year, EntryDate) + '_' + RIGHT('00' + DATEPART(month, EntryDate), 2) Personally, I might use convert(): select replace(convert(varchar(7), EntryDate,...

There is no way to use a variable but you can work around it: case when @part = 'YEAR' THEN DATEPART(YEAR, cd.TurninDate) when @part = 'WEEK' THEN DATEPART(WEEK, cd.TurninDate) ... END The design of the SQL/T-SQL language is lacking as you can see....

Create a set of Months as the first table in the from clause, and join your query to this. Then you will get a result for every month. I have similar issues with financial reporting where I need results for all months and financial years. I have used the DATENAME...

Arrg! Don't use LIKE with dates. How about just doing something like this: where DATEPART(month, ReleaseDate) = DATEPART(month(GETDATE()) AND DATEPART(day, ReleaseDate) = DATEPART(day, GETDATE()) Or, more simply: where month(ReleaseDate) = month(getdate()) and day(ReleaseDate) = day(getdate()) ...

You can do this way: SELECT YEAR ( MAX(date) ) as year, MONTH ( MAX(date) ) as month, DAY ( MAX(date) ) as day, HOUR( MAX(date) ) as hour, MINUTE( MAX(date) ) as minute, SECOND( MAX(date) ) as second from table ...

Assuming I understand the data that needs to be returned, what you need is a list of each Table1 record with any Table2 records that match on the ID attribute and whose B_Year value is between P_StartYear and P_EndYear, inclusive. If you could transform this record: ID P_Startyear P_Endyear -----------------------------------...