date headache - 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 2005 Forums

Transact-SQL (2005)

date headache

Author

Topic

evvo
Starting Member

16 Posts

Posted - 2008-07-29 : 07:31:15

im working on a stored procedure that outputs the days of the week into a dd using a start and end date, and deleting sundays

at workplace its fine, uses a date formet 'dd/mm/yyyy'

however my machine at home doesnt like this format.

iget an error
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

if i change date format to yyyy-mm-dd it runs but i get the dates incorrect, ie it sets a friday as a saturday and deletes saturdays not sundays, its as if its a day forward.

i checking weekends with

Update Calendar
set Calendar.day = 'Saturday'
where datepart(dw,date) in (6)

-- DELTE SUNDAYS
DELETE dbo.Calendar
WHERE DATEPART(DW,Date) = 7

as i say this works fine with dd/mm/yyyy but my machine cant use this formet. any ideas i need to solve this asap

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-07-29 : 07:43:26

The settings are probably different. Check out dateformat and datefirst...

http://msdn.microsoft.com/en-us/library/ms189491.aspx
http://msdn.microsoft.com/en-us/library/ms181598.aspx

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

evvo
Starting Member

16 Posts

Posted - 2008-07-29 : 07:50:46

thanks

treid

SET DATEFORMAT dmy
SET DATEFIRST 1

at top of SP but still get same error

evvo
Starting Member

16 Posts

Posted - 2008-07-29 : 07:53:43

is there a setting on my machine ican change?

there is some difference between this and work as it accepts 'dd/mm/yyyy' when this SP runs at work.

is it a machine setting or sql server?

evvo
Starting Member

16 Posts

Posted - 2008-07-29 : 08:06:23

its like my machine is seeing sunday as first day of the week.

whatever i set datefirst to has no effect.

is there another way to override what sql sees as first day of week

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-30 : 04:02:14

What is the datatype of date column?
Can you post the full query that caused the error?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources