Convert a Date to a Day of the Week.

Hey, Im pretty sure this is possible, but let me know if it isnt.

I have a table of dates in the format "DD/MM/YYYY", is there a way of converting it to output the day of the week? for example 16/11/2007 would display Friday insted.

Thanks in advance John

selectdatepart(dw,GETDATE())

selectdatename(dw,GETDATE())

|||

Hi u can check out this

select datepart(dw,date_created) from users

select datename(dw,date_created) from users

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||

Thanks to the both of you, both methods worked amazingly faultlessly. Thanks John

Conversion between Date Formats

Hi. I have a DB in which we store dates in yyyy/mm/dd. However when we
want to display this date via a web frontend, it needs to be in
dd/mm/yyyy. I've declared a function (shown below) which converts
between these date formats and returns a varchar(20). This works fine
however now I need to have the ability to sort on this date field in
the frontend. This requires my function to return a datetime in the
required format. Can this be done?

DECLARE @.InputDate nvarchar(20)
DECLARE @.OutputDate nvarchar(20)
DECLARE @.Day nvarchar(2)
DECLARE @.Month nvarchar(2)
DECLARE @.Year nvarchar(4)
DECLARE @.Time nvarchar(12)

SET @.InputDate = '2005/03/01 14:30:00'
SET @.Day = cast(datepart(day,@.InputDate) as nvarchar(2))
SET @.Month = cast(datepart(month,@.InputDate) as nvarchar(2))
SET @.Year = cast(datepart(year,@.InputDate) as nvarchar(4))
SET @.Time = substring(cast(@.InputDate as nvarchar(23)),12,12)

SET @.OutputDate = replicate('0',2-len(@.Day)) + @.Day + '/' +
replicate('0',2-len(@.Month)) + @.Month + '/' +
@.Year + ' ' + @.Time

SELECT @.OutputDate AS OutputDate

Thx
VilenReturn dates as dates and format them for display in the front end or
middle tier. Some users may prefer to format them differently to the
way you do.

In the database dates should be stored as DATETIME or SMALLDATETIME
datatypes. These DO NOT have any fixed format and will always sort
chronologically. It isn't a good idea to sort on a function or
expression if you can avoid it.

--
David Portas
SQL Server MVP
--

continuous dates

Hi Guys,
I have a sql query that returns a count of a certain column say the
number of bugs per w on a w by w basis. there are some ws
which have no bugs and these ws are not returned. How can I write a
sql query that has all the ws whether there were bugs or not and
return 0 for the ws where there were no bugs.
for example:
at the moment:
w no. of bugs
22/1/06 10
15/1/06 5
1/1/06 2
(w 8/1/06 is missing)
what I expect :
w no. of bugs
22/1/06 10
15/1/06 5
8/1/06 3
1/1/06 2
infact I want all the ws from 1970 to present
Thanking you,
Regards,
NJYou need to provide more information to get an accurate response. It would
greatly help to see the table involved and some sample data. What is the
criteria for determining if there is a bug or not? Are you summing rows
with similar dates or is there a BUG column that has a value in it. Your
example does not make sense in that you state 8/1/06 is missing yet you show
it as having 3 bugs.
Andrew J. Kelly SQL MVP
"NJ" <npaulus@.hotmail.com> wrote in message
news:1137886146.073541.14890@.g43g2000cwa.googlegroups.com...
> Hi Guys,
> I have a sql query that returns a count of a certain column say the
> number of bugs per w on a w by w basis. there are some ws
> which have no bugs and these ws are not returned. How can I write a
> sql query that has all the ws whether there were bugs or not and
> return 0 for the ws where there were no bugs.
> for example:
> at the moment:
> w no. of bugs
> 22/1/06 10
> 15/1/06 5
> 1/1/06 2
> (w 8/1/06 is missing)
> what I expect :
> w no. of bugs
> 22/1/06 10
> 15/1/06 5
> 8/1/06 3
> 1/1/06 2
> infact I want all the ws from 1970 to present
> Thanking you,
> Regards,
> NJ
>|||Thanks for yor reply Andrew
I am summing rows with similar dates. and sorry about the 8/1/06 too it
should have 0 bugs. It doesnt show in the output of the first query
because there are no bugs during the w 8/1/06 to 14/1/06. In my
table everytime there is a bug it is entered into the database along
with the date it happened.
my query then finds the w (starting date of the w when the bug
occured i.e if the bug occoured on the 11 th jan 2006 then it becomes
8/1/06 as 8th is the starting day of the w) and counts all the rows
with similar dates. ie: I group by w. Because of this there are
ws when no bugs occured and hence dont show up in the query result.
however I want to be able to return that date with a 0 for the number
of bugs for that w.
I hope that explains a bit better Andrew.
Regards,
NJ|||Thanks for yor reply Andrew
I am summing rows with similar dates. and sorry about the 8/1/06 too it
should have 0 bugs. It doesnt show in the output of the first query
because there are no bugs during the w 8/1/06 to 14/1/06. In my
table everytime there is a bug it is entered into the database along
with the date it happened.
my query then finds the w (starting date of the w when the bug
occured i.e if the bug occoured on the 11 th jan 2006 then it becomes
8/1/06 as 8th is the starting day of the w) and counts all the rows
with similar dates. ie: I group by w. Because of this there are
ws when no bugs occured and hence dont show up in the query result.
however I want to be able to return that date with a 0 for the number
of bugs for that w.
I hope that explains a bit better Andrew.
Regards,
NJ|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Guessing at what you have, build a report range table:
CREATE TABLE WRanges
(w_nbr INTEGER NOT NULL PRIMARY KEY,
w_start DATETIME NOT NULL,
w_finish DATETIME NOT NULL,
CHECK (w_start, w_finish));
then do a query like this:
SELECT W.w_nbr, W.w_start, COUNT(B.bug_id)
FROM WRanges AS W
LEFT OUT JOIN
BugReport AS B
ON B.bug_date BETWEEN W.w_start AND W, w_finish
GROUP BY W.w_nbr, W.w_start;|||
--CELKO-- wrote:

>Please post DDL, so that people do not have to guess what the keys,
>constraints, Declarative Referential Integrity, data types, etc. in
>your schema are. Sample data is also a good idea, along with clear
>specifications. It is very hard to debug code when you do not let us
>see it.
>
True. Unfortunately, not letting us see this code of yours
would have been much kinder. Slop, slop, slop.

>Guessing at what you have, build a report range table:
>CREATE TABLE WRanges
>(w_nbr INTEGER NOT NULL PRIMARY KEY,
> w_start DATETIME NOT NULL,
> w_finish DATETIME NOT NULL,
> CHECK (w_start, w_finish));
>
CHECK what?

>then do a query like this:
>SELECT W.w_nbr, W.w_start, COUNT(B.bug_id)
> FROM WRanges AS W
> LEFT OUT JOIN
>
LEFT OUT JOIN?

> BugReport AS B
> ON B.bug_date BETWEEN W.w_start AND W, w_finish
>
W, w_finish? And for that matter, how is BETWEEN going
to work at all?. Do Sunday bugs get counted in two separate
ws, or do they not get counted at all? Who knows?

>GROUP BY W.w_nbr, W.w_start;
>
>
Steve Kass
Drew University|||On 21 Jan 2006 15:29:06 -0800, NJ wrote:

>Hi Guys,
>I have a sql query that returns a count of a certain column say the
>number of bugs per w on a w by w basis. there are some ws
>which have no bugs and these ws are not returned. How can I write a
>sql query that has all the ws whether there were bugs or not and
>return 0 for the ws where there were no bugs.
Hi NJ,
Use a calendar table (www.aspfaq.com/2519).
SELECT c.dt, COUNT(y.BugDate)
FROM Calendar AS c
LEFT OUTER JOIN YourTable AS y
ON y.BugDate >= c.dt
AND y.BugDate < DATEADD(day, 7, c.dt)
WHERE c.dayname = 'Sunday'
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP