Thanks for the answers, seems like Mark-101232 had the shortest one.

SELECT col FROM (VALUES (1), (2), (3)) x(col)

@jeff Moden - Not really, they just happens to be sequential,...

Perfect!

Nice trick with "& 2147483647", haven't used bit wise and before, interesting.

The number of colors is dynamic.

@sean Pearce - Sorry was away at lunch, the desired result would be '2013-02-15'

Yeah! The last query you've posted worked really well! Thanks!

naturally, thanks 🙂

Does it make any sense at all?

That is true, i had to change the data to demonstrate the original IF specifications that i specified above.

Hmm, to clarify it lets look at two sets:

With the table below:

INSERT...

Yes, but you've missed that IF MAX(mydate)<GETDATE() THEN MAX(mydate)

Your WHERE clause truncates all dates that are less then current date "where mydate >= GETDATE()"

This can be seen with this values

INSERT...

Yey! Added "everyone" as a user, restarted the server and msdb works now!

Well, now we know for sure that it was rights issue, ill figure out which account to add...

I must admit I'm pretty bad with right permissions...

@gilamonster

This is the error from windows logs:

Login failed for user 'Server\User'. Reason: Failed to open the explicitly specified database 'msdb'....

It is still in recovery...

However, Ive checked permissions of the origin folder and they had MSSQLSERVER as a user.

Ive tried to search for that user but couldn't find...

I'm sure that the paths are right, I've pasted them from the query into windows run window and it found both of the files.

Is there any way to test if...

Do you mean windows permission?

I get this error when i try to connect to the server:

Cannot display policy health state at the server level, because the user doesn’t have permission....

Collations cannot be changed specifically for columns, they effect the whole DB.

But you can temporarily change them like this:

SELECT top 10 firstname COLLATE SQL_Latin1_General_CP1_CI_AS FROM users

@cadavre - Thats that did the trick!

@celko - Very useful insight, i like it. One thing i don't understand is:

That is 'yyyy-mm-00' for a month...