SET DATEFIRST - 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)

SET DATEFIRST

Author

Topic

Jonny1409
Posting Yak Master

133 Posts

Posted - 2007-10-23 : 08:12:15

Hello,

I've read (on this forum) that you can't use 'SET DATEFIRST 7' or such like in a user defined function.

How can I get round this please?

The reason I tried to put this in, is that when I use the following code :


DATEPART(dw,@myDate)

If myDate = '27/12/2007' it shows as 4 (Wednesday), however It should be 5 (Thursday).

In Query Analyser, if I put this SET DATEFIRST 7 in, it shows correctly as 4 (Thursday), but obviously this can't be used in a UDF

Thanks in advance.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-23 : 08:15:34

this is a connection setting so youhave to set it on your connection that you're accessing the sql server on.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-23 : 08:18:51

[code]SELECT CASE DATENAME(dw, '20071227')
WHEN 'Monday' THEN 2
WHEN 'Tuesday' THEN 3
WHEN 'Wednesday' THEN 4
WHEN 'Thursday' THEN 5
WHEN 'Friday' THEN 6
WHEN 'Saturday' THEN 7
WHEN 'Sunday' THEN 8
END[/code]

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-23 : 08:48:15

quote:

Originally posted by Peso

SELECT	CASE DATENAME(dw, '20071227')
		WHEN 'Monday' THEN 2
		WHEN 'Tuesday' THEN 3
		WHEN 'Wednesday' THEN 4
		WHEN 'Thursday' THEN 5
		WHEN 'Friday' THEN 6
		WHEN 'Saturday' THEN 7
		WHEN 'Sunday' THEN 81
	END

E 12°55'05.25"
N 56°04'39.16"

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-23 : 08:51:14

Thanks!

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources