Ever been faced with this error message?
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
This arises from different databases being built on diffferent versions of SQL Server by different developers and when you wanht to make them talk to each other one of them says something like "I've no idea what he's on about, slap a Babel Fish in my ear and let's try again". The Babel Fish in question is a simple piece of code.
Usually this works: COLLATE database_default but sometimes for me I need to use COLLATE Latin1_General_CS_AS depending on what systems i'm working with.
Where is it used? Here:
SELECT
a.fieldname1
,a.fieldname2
,b.fieldname3
FROM table1 a
INNER JOIN table2 b ON a.id=b.id COLLATE database_default
WHERE id = 1
In case you didn't spot it, its sitting on the end of the INNER JOIN line. Sometimes you'll just have to experiment with where it goes. If you're using an inline query, it'll go in there. If you engaging in some complicated WHERE clause fruitiness, then it may need to go in there. Wherever it goes, its your saviour.
Thursday, 3 June 2010
Tuesday, 1 June 2010
So here we go with some useful DATETIME functions.
Dates and times are to be found at the very core of SQL programming, especiually when, like me, you're dealing with SQL Reports.
To convert a date time into zero hours and mins on that date:
SELECT DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)
The example above uses GETDATE() but you could replace that with any DATETIME field to make the conversion scaleable.
You may want to set a report date start and end parameters to automatically be the first and last day of the previous week, or month, or the current month to date. These two cheeky little monkeys will give you the first and last days of the previous month:
SELECT dDATEADD(mm,-1,DATEADD(month,DATEDIFF(month,0,GETDATE()),0)) as FirstDayOfPreviousmonth
SELECT DATEADD(s,-1,DATEADD(month,DATEDIFF(month,0,GETDATE()),0)) as LastDayOfPreviousmonth
To get the dates for current month so far, use this as your start date:
SELECT DATEADD(month,DATEDIFF(month,0,GETDATE()),0) as FirstDayOfCurrentmonth
and GETDATE() as your end date
What about the last full week (assuming here that you work from Monday to Sunday)?
SELECT DATEADD(ss,-1,DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)) as MondayMorningOfLastWeek
SELECT DATEADD(wk,-1,DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)) as LateLastSundayEvening
To find the start of the current financial year (In the UK, the financial year runs from 1st April):
SELECT DATEADD(month,-CASE WHEN DATEPART(month,GETDATE())>3 THEN DATEPART(month,GETDATE())-4 ELSE DATEPART(month,GETDATE()) + 8 END,DATEADD(month, DATEDIFF(month,0, GETDATE()),0)) as FiscalYearStart (This probably doesn't need to be quite so complicated as this, why don't you have a go at simplifying it?)
There are all sorts of cunning tricks you can do with DATETIMEs and some can be a bit confusing, at least until someone explains it so that's what we'll do here. Let's break down the first function I showed you:
SELECT DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)
We'll work through it in tutorial style, so type SELECT GETDATE() into your SQL Server 2005 Management Studio query pane.
When I do this it gives me: 2010-06-01 15:26:37.937 which is the current date and time to the nearest milisecond. Now wrap that GETDATE function in a DATEDIFF so it looks like this: SELECT DATEDIFF(DAY,0,GETDATE()) and when you press execute it should output something like this: 40328
This number represents the DATE DIFFERENCE in DAYS between day zero and today. Day zero is January 1st 1900. Now we wrap that code in a DATEADD like so: SELECT DATEADD(DAY,0,DATEDIFF(DAY,0,GETDATE())) which outputs 2010-06-01 00:00:00.000. What we've done there is to say take day zero, and add 40328 days onto it and because day zero is set to midnight, then when we add forty odd thousand days onto it, it still comes out as midnight.
So to put all that in laymans terms, we're saying How many days is it since 1st January 1900? Let's add that number of days ONTO 1st Jan 1900 to get the date and time of midnight today (or last night).
You can break down all the code above in similar terms and you'll soon have an understanding of how it all works.
Happy squirelling.
Dates and times are to be found at the very core of SQL programming, especiually when, like me, you're dealing with SQL Reports.
To convert a date time into zero hours and mins on that date:
SELECT DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)
The example above uses GETDATE() but you could replace that with any DATETIME field to make the conversion scaleable.
You may want to set a report date start and end parameters to automatically be the first and last day of the previous week, or month, or the current month to date. These two cheeky little monkeys will give you the first and last days of the previous month:
SELECT dDATEADD(mm,-1,DATEADD(month,DATEDIFF(month,0,GETDATE()),0)) as FirstDayOfPreviousmonth
SELECT DATEADD(s,-1,DATEADD(month,DATEDIFF(month,0,GETDATE()),0)) as LastDayOfPreviousmonth
To get the dates for current month so far, use this as your start date:
SELECT DATEADD(month,DATEDIFF(month,0,GETDATE()),0) as FirstDayOfCurrentmonth
and GETDATE() as your end date
What about the last full week (assuming here that you work from Monday to Sunday)?
SELECT DATEADD(ss,-1,DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)) as MondayMorningOfLastWeek
SELECT DATEADD(wk,-1,DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)) as LateLastSundayEvening
To find the start of the current financial year (In the UK, the financial year runs from 1st April):
SELECT DATEADD(month,-CASE WHEN DATEPART(month,GETDATE())>3 THEN DATEPART(month,GETDATE())-4 ELSE DATEPART(month,GETDATE()) + 8 END,DATEADD(month, DATEDIFF(month,0, GETDATE()),0)) as FiscalYearStart (This probably doesn't need to be quite so complicated as this, why don't you have a go at simplifying it?)
There are all sorts of cunning tricks you can do with DATETIMEs and some can be a bit confusing, at least until someone explains it so that's what we'll do here. Let's break down the first function I showed you:
SELECT DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)
We'll work through it in tutorial style, so type SELECT GETDATE() into your SQL Server 2005 Management Studio query pane.
When I do this it gives me: 2010-06-01 15:26:37.937 which is the current date and time to the nearest milisecond. Now wrap that GETDATE function in a DATEDIFF so it looks like this: SELECT DATEDIFF(DAY,0,GETDATE()) and when you press execute it should output something like this: 40328
This number represents the DATE DIFFERENCE in DAYS between day zero and today. Day zero is January 1st 1900. Now we wrap that code in a DATEADD like so: SELECT DATEADD(DAY,0,DATEDIFF(DAY,0,GETDATE())) which outputs 2010-06-01 00:00:00.000. What we've done there is to say take day zero, and add 40328 days onto it and because day zero is set to midnight, then when we add forty odd thousand days onto it, it still comes out as midnight.
So to put all that in laymans terms, we're saying How many days is it since 1st January 1900? Let's add that number of days ONTO 1st Jan 1900 to get the date and time of midnight today (or last night).
You can break down all the code above in similar terms and you'll soon have an understanding of how it all works.
Happy squirelling.
Introducing the SQL Soldier
Hi, I'm Nick Holt. I'm 37 and I'm an ICT Management Information Specialist working for a company in Manchester, UK. I use SQL Server 2000 and 2005, plus Visual Studio 2003 and 2005. Reporting Services is what takes up most of my time but i'm currently working towards certification in the form of a MCTS qualification in SQL Server 2005 Business Intelligence Implementation and Maintenance which incorporates Reporting Services, Integration Services and Analysis Services. I'll keep you updated on those as I come up with anything.
I also use MySQSL which, in conjunction with HTML, CSS and PHP, I use to code my own personal website which is dedicated to Bury Football Club and can be found at http://www.mannyroadend.co.uk.
The intention of this blog is to share bits of code I create, cool functions and cunning solutions to the sorts of problems you might face in your day to day SQL lives.
I also use MySQSL which, in conjunction with HTML, CSS and PHP, I use to code my own personal website which is dedicated to Bury Football Club and can be found at http://www.mannyroadend.co.uk.
The intention of this blog is to share bits of code I create, cool functions and cunning solutions to the sorts of problems you might face in your day to day SQL lives.
Subscribe to:
Posts (Atom)