date of the week? 1

[DOGWATCH]

I need a small function that starts from
@eventDate = '2004-14-03 01:16:12.000'

THEN
adds one day to that date and then determines
the day of the week from that date

I also need to convert '2004-14-03 01:16:12.000'
into '03/14/2004' type of string format.

I know datepart(dw, 3/1/2004') will work fine giving you a numerical value for day of the week.

but the above format '2004-14-03 01:16:12.000' plugged
in just makes that blow up.

In this case '2004-14-03 01:16:12.000' + 1 day
should return '1' which corresponds for monday.

Any ideas ????

 

[checkai]

convert(char(10),@eventDate,101)

 

[bdreed35]

This will add one day, Monday as the first day of the week, and return the day of the week.

declare @eventDate datetime
set @eventDate = convert(datetime,'2004-03-14 01:16:12.000')
set datefirst 1
select datepart(dw,@eventDate+1)

checkai's formula takes care of the formatting the date into a string.
I had to move the 14 and the 03 in the date time that you provided. It may be a setting but it wouldn't except the 14 where you had it because it assumed that was the month position.

~Brian

 

[DOGWATCH]

Actually that returns returns '2004-13-03'

this code

SELECT @eventDate = '2004-13-03 01:16:12.000'
SELECT @smallDate = convert(char(10),@eventDate,101)  
SELECT @dayoftheWeek = DATEPART(dw, '2004-13-03' ) + 1
SELECT @smallDate, @dayoftheWeek

will blow up but

but this code

SELECT @eventDate = '2004-13-03 01:16:12.000'
SELECT @smallDate = convert(char(10),@eventDate,101)  
SELECT @dayoftheWeek = DATEPART(dw, '3/13/2004' ) + 1
SELECT @smallDate, @dayoftheWeek

works fine if I can get the conversion in the right
format it should work fine. Any ideas??

 

[bdreed35]

Well, I don't think you want to add 1 after you determine the day of the week. What happens when the day of the week is 7?
I would change your code to this so that you don't have to worry about the date format:

SELECT @eventDate = '2004-13-03 01:16:12.000'
SELECT @smallDate = convert(char(10),@eventDate,101)  
SELECT @dayoftheWeek = DATEPART(dw, @eventDate + 1) 
SELECT @smallDate, @dayoftheWeek

~Brian

 

[MDXer]

to add a day to the date use the DateAdd() function. I ran this and it results in

------------------------------------------------------ ---------------
2004-03-13 00:00:00 1

Declare @eventdate varchar(50)
Declare @smallDate smalldatetime
Declare @dayoftheweek varchar(15)

SELECT @eventDate = '2004-03-13 01:16:12.000'
SELECT @smallDate = convert(varchar,Cast(@eventDate as smalldatetime),101)
SELECT @dayoftheWeek = DATEPART(dw, DateAdd(dd,1,@smallDate))

SELECT @smallDate, @dayoftheWeek

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[DOGWATCH]

AWESOME now I have the first date, next date, number of days of the vacation and end date!

NICELY DONE!

DECLARE @message Varchar(50)
DECLARE @eventdate varchar(50)
DECLARE @startDate smalldatetime
DECLARE @dayoftheweek varchar(15)
DECLARE @endDate varchar(50)
DECLARE @vacationLen int
DECLARE @firstDate smalldatetime 

SELECT @message = 'ON VACATION 03/24/04'
SELECT @eventDate = '2004-03-17 01:16:12.000'
SELECT @startDate = convert(varchar,Cast(@eventDate as smalldatetime),101)
SELECT @endDate = SUBSTRING(@message, 13, 8)
SELECT @firstDate =  DateAdd(dd,1,@startDate)
	
SET DATEFIRST  1
SELECT @dayoftheWeek = DATEPART(dw, DateAdd(dd,1,@startDate))
SELECT @vacationLen = datediff(day, @startDate, @endDate)
SELECT @startDate, @firstDate, @dayoftheWeek, @message, @endDate, @vacationLen

 

[MDXer]

Glad you got it working. Thanks for the star.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck