DATEPART() Function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

DATEPART() Function

Author

Topic

Tonic
Starting Member

26 Posts

Posted - 2006-05-22 : 08:23:28

I have two entries in a table

site_ref src_date budget_rent budget_occ

AE 01/04/2006 123456.0000 69

AE 01/05/2006 371540.2432 74

When I run the following query

SELECT site_ref, budgeted_net_rent, budgeted_occupancy
FROM src_tbl_rental
WHERE (src_date >= @dt_src_date) AND (src_date < DATEADD(month, 1, @dt_src_date) - 1)

I get a result set that includes both of the above lines, when what I was trying to achieve was just the value for the first one only. I know that the date is entered in the database as 01/04/2006 as part of an import from excel. Not sure wther it is actually yyyy-mm-dd etc in reality, but from a visual point of view it certainly is dd/mm/yyyyy.

If I manually enter the (@dt_src_date) parameter of 01/04/2006 then it returns only the one line. The problem I have is that the parameter that I mentioned actually needs to provide dd/mm/yyyy data for the bulk of the other functions etc, I want this query to only return a value that will always the mm/yyyy element of the parameter value and prefixed by 01/.

Anybody help me out here?

Regards

Toni C
aka Talisa

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-22 : 08:29:39

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=14548

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2006-05-22 : 08:39:54

1) You should be using a DATETIME datatype to store your dates - then the date can be presented and manipulated appropriately. If you are using a string datatype for your columns, such as VARCHAR, then pretty much all bets are off on what will happen

2) Dates which are in text strings should always be "given" to SQL Server in "yyyymmdd" format; or you must use CONVERT(datetime, 'MyStringDate', 999) [999=Conversion style, see Books Online for details] or SET DATEFORMAT dmy [arrange "d", "m" and "y" letters in approprate order]. Otherwise "31/12/2006" is unambiguous whereas "01/02/03" is ambiguous to SQL Server for Day, Month, Century and Year!

Kristen

Tonic
Starting Member

26 Posts

Posted - 2006-05-22 : 08:46:08

Thank you for your informative responses. I solved this by using

WHERE (src_date >= CONVERT(varchar(6), @dt_src_date, 112) + '01') AND (src_date < DATEADD(month, 1, CONVERT(varchar(6), @dt_src_date, 112) + '01') - 1)

Regards

Toni C
aka Talisa

Kristen
Test

22859 Posts

Posted - 2006-05-22 : 09:01:32

Is @dt_src_date a DATETIME datatype, or a String (varchar etc?)

Because if its DATETIME already then there are much more efficient (if rather obtuse!!) ways to get 1st / last day of the month

Also your final "- 1" is going to mean that you will NOT match src_date when it is the last day in the month.


DECLARE	@dt_src_date	datetime
SET	@dt_src_date = '20060522'

DECLARE	@TestData TABLE
(
	src_date datetime
)

INSERT INTO @TestData
SELECT '20060501' UNION ALL
SELECT '20060522' UNION ALL
SELECT '20060531'		-- Last day of month check

-- Your way
SELECT	*
FROM	@TestData
WHERE	    (src_date >= CONVERT(varchar(6), @dt_src_date, 112) + '01') 
	AND (src_date < DATEADD(month, 1, CONVERT(varchar(6), @dt_src_date, 112) + '01') - 1)

-- My way !!
SELECT	*
FROM	@TestData
WHERE	    (src_date >= DATEADD(Month, DATEDIFF(Month, 0, @dt_src_date), 0))
	AND (src_date <  DATEADD(Month, DATEDIFF(Month, 0, @dt_src_date)+1, 0))

Kristen

Tonic
Starting Member

26 Posts

Posted - 2006-05-22 : 11:40:25

Look Kristen, you only have 8075 Posts so it does not mean that you know it all. :)

Damn it, you are right, it is likely that the last day of the month will come up every now and again and the code I am using may falter as a result. I will use your method as that seems to be the safest. Another snippet of info to be stored away in my head somewhere.

Out of curiousity, I have a Samms T-SQL in 21 Days (phah, yeah right) book, what is a good book that you recommend. I find that I learn so much more from some posts on various forums (such as this one) than any amount of text book stuff. Sort feels like the real world out here in forum land. Any suggestions?

Thanks for your help, was only joking about the posts thing.

Kindest Regards

Toni C
aka Talisa

Kristen
Test

22859 Posts

Posted - 2006-05-22 : 12:36:21

"it is likely that the last day of the month will come up every now and again"

Every month in fact!

"Samms T-SQL in 21 Days"

Its a good book. When you are getting more "expert" then you'll find the books by Ken Henderson and Kalen Delaney very useful.

Have a look at http://www.sqlteam.com/store.asp

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources