Cannot get date in the right format

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

General SQL Server Forums

New to SQL Server Programming

Cannot get date in the right format

Author

Topic

Gigi
Starting Member

23 Posts

Posted - 2009-06-07 : 12:52:42

hello everyone,
I have a function that needs to check if its a weekday or not. the function is
ALTER function [dbo].[udf_Dayofweek](@dtDate DATETIME)
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE @rtDayofWeek VARCHAR (10)
select @rtDayofWeek = CASE DATEPART (Weekday, @dtDate)
when 1 then 'Sunday'
when 2 then 'Monday'
when 3 then 'Tuesday'
when 4 then 'Wednesday'
when 5 then 'Thursday'
when 6 then 'Friday'
when 7 then 'Saturday'
end
return (@rtDayofWeek)
end

then I call the function in a CASE statement and if its a weekday then I set the date to date-1 and I would ideally like to change the format to mm/dd/yyyy.
This is what I am doing
update dates
set startdate = case
when dbo.udf_DayOfWeek (getdate()) = 'monday' then dateadd(dd,-3, getdate())
when dbo.udf_DayOfWeek (getdate()) = 'tuesday' then dateadd(dd,-1, getdate())
when dbo.udf_DayOfWeek (getdate()) = 'wednesday' then dateadd(dd,-1, getdate())
when dbo.udf_DayOfWeek (getdate()) = 'thursday' then dateadd(dd,-1, getdate())
when dbo.udf_DayOfWeek (getdate()) = 'friday' then dateadd(dd,-1, getdate())
else 'nothing'
end

the above statement changes the date but the format of the date is 2009-06-06 12:41:32.157 (the startdate column is of datetime data type)

now I tried changing the date format in the CASE statement by doign the following
update dates
set startdate = case
when dbo.udf_DayOfWeek (getdate()) = 'monday' then cast(datepart(month, getdate()) as varchar(2)) + '/' + cast(datepart(day, getdate()-3) as varchar(2)) +'/'+ cast(datepart(year, getdate()) as varchar(4))
when dbo.udf_DayOfWeek (getdate()) = 'tuesday' then cast(datepart(month, getdate()) as varchar(2)) + '/' + cast(datepart(day, getdate()-1) as varchar(2)) +'/'+ cast(datepart(year, getdate()) as varchar(4))
when dbo.udf_DayOfWeek (getdate()) = 'wednesday' then cast(datepart(month, getdate()) as varchar(2)) + '/' + cast(datepart(day, getdate()-1) as varchar(2)) +'/'+ cast(datepart(year, getdate()) as varchar(4))
when dbo.udf_DayOfWeek (getdate()) = 'thursday' then cast(datepart(month, getdate()) as varchar(2)) + '/' + cast(datepart(day, getdate()-1) as varchar(2)) +'/'+ cast(datepart(year, getdate()) as varchar(4))
when dbo.udf_DayOfWeek (getdate()) = 'friday' then cast(datepart(month, getdate()) as varchar(2)) + '/' + cast(datepart(day, getdate()-1) as varchar(2)) +'/'+ cast(datepart(year, getdate()) as varchar(4))

but it still gives me the date in 2009-06-06 00:00:00.000

but if i just do a select with the cast statement
select cast(datepart(month, getdate()) as varchar(2)) + '/' + cast(datepart(day, getdate()-1) as varchar(2)) +'/'+ cast(datepart(year, getdate()) as varchar(4))

the result is exactly what I want
6/6/2009

What i need help is to be pointed out why I am not getting the date format when I put the cast statment in the CASE statement.

Any help would be greatly appreciated.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-07 : 13:15:56

Your destination column is of type datetime.
You cannot store a datetime value in your most liked format.
datetime is always datetime and stores the information but no formatting.

Formatting should happen in your app or in your select when retrieving the date from that table.

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-07 : 13:21:43

i think you get date with 0 timepart as startdate is datetime. date time always stores date as well as time part.if there is no time part to store it stores 00:00:00. that shouldnt be a problem as you can always use date formatting functions in your front end application to get date value in your required format.

Gigi
Starting Member

23 Posts

Posted - 2009-06-07 : 14:09:09

Webfred,
but say if I change the data type in the dates colum t varchar..would that help....how can i get date -1 if its a weekday...can you point me how i can achieve my obejective.....I am really out of ideas

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-07 : 14:22:44

So maybe I have not understood what your problem is.
I thought to get your result from getdate() - X days is no problem.
I thought you have only a problem with the format?

No, you're never too old to Yak'n'Roll if you're too young to die.

Gigi
Starting Member

23 Posts

Posted - 2009-06-07 : 15:21:35

i need to be able to change the date and format in one statement

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-07 : 16:05:38

OK,
so you have a date coming from getdate().
You are calculating this date - X days.
You are formatting this calculated date.
The result is like this: 6/6/2009 and this is varchar.
Because your statement is an update statement to datetime column in a table, sql server converts it back to a datetime format.
If you would like to keep the 6/6/2009-format, then it is only possible if your column in table is like varchar(10).

But I would suggest you, not to do that.
You can always format the datetime to a pleasing format while retrieving the data from the table.

Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

Gigi
Starting Member

23 Posts

Posted - 2009-06-08 : 08:29:45

webfed,
My only problem here is how to program if the day is monday in which case i would need to get friday's date that would be day-3. this is why i have a function that first checks what day of the week it is and then 'updates' the column. On a side note, I have the Dates table containing only dates and the values are pulled to another table which does most of the processing.

Can you or someone else assist me where I can have a process where it gives me day -1 and if its a monday then it gives me day-3 and the date format should be mm/dd/yyyy

regards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources