Day of the week function

Is it any function that return name of the day on week .. like Monday,
Tuesday etc.?
ThanksYou could use the format function:
format(fieldname,"ddd")
Mike G.
"Carlos" <cp@.swa.com> wrote in message
news:e8XUzAgFFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Is it any function that return name of the day on week .. like Monday,
> Tuesday etc.?
> Thanks
>|||You can also use the WeekDay function:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctweekday.asp
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mike G." <theNOSPAMjunkbox@.comcast.net> wrote in message
news:OV2LhegFFHA.560@.TK2MSFTNGP15.phx.gbl...
> You could use the format function:
> format(fieldname,"ddd")
> Mike G.
>
> "Carlos" <cp@.swa.com> wrote in message
> news:e8XUzAgFFHA.1476@.TK2MSFTNGP09.phx.gbl...
>> Is it any function that return name of the day on week .. like Monday,
>> Tuesday etc.?
>> Thanks
>|||In addition there is a WeekDayName function:
http://msdn.microsoft.com/library/en-us/vblr7/html/vafctWeekdayName.asp
> You can also use the WeekDay function:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctweekday.asp
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Mike G." <theNOSPAMjunkbox@.comcast.net> wrote in message
> news:OV2LhegFFHA.560@.TK2MSFTNGP15.phx.gbl...
>> You could use the format function:
>> format(fieldname,"ddd")
>> Mike G.
>>
>> "Carlos" <cp@.swa.com> wrote in message
>> news:e8XUzAgFFHA.1476@.TK2MSFTNGP09.phx.gbl...
>> Is it any function that return name of the day on week .. like Monday,
>> Tuesday etc.?
>> Thanks
>>
>sql

Day of the week

Is it any function that return name of the day on week .. like Monday,
Tuesday etc.?
Thanks
SELECT DATENAME(w, GETDATE())
Look into BOL for all the other date functions.
Thanks
Yogish
|||DATENAME()
David Portas
SQL Server MVP

Day of the week

Is it any function that return name of the day on week .. like Monday,
Tuesday etc.?
ThanksSELECT DATENAME(w, GETDATE())
Look into BOL for all the other date functions.
--
Thanks
Yogish|||DATENAME()
--
David Portas
SQL Server MVP
--

Day of the week

Is it any function that return name of the day on week .. like Monday,
Tuesday etc.?
ThanksSELECT DATENAME(w, GETDATE())
Look into BOL for all the other date functions.
Thanks
Yogish|||DATENAME()
--
David Portas
SQL Server MVP
--

day name of a date

Hello,
One of my group field is a date, and I need to return day name, like Monday,
Tuesday, .. how can I do this?
Thanks,Imagine you have OrderDate; then the exression could be
=WeekDayName(WeekDay(Fields!OrderDate.Value))
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:9F7027F2-7A04-4D0A-9176-63514D0599AA@.microsoft.com...
> Hello,
> One of my group field is a date, and I need to return day name, like
Monday,
> Tuesday, .. how can I do this?
> Thanks,
>sql

DATETIME To Format mm/dd/yyyy hh:mm am/pm

I have a column in a database set as a DATETIME datatype, when I select it, I want to return it as:

mm/dd/yyyy hh:mm am or pm.

How in the world can I do this? I looked at the function CONVERT() and it doesnt seem to have this format as a valid type. This is causing me to lose my hair, in MySQL it is just so much easier. .

At any rate, currently when I select the value without any convert() it returns as:

June 1 2007 12:23AM

Which is close, but I want it as:

06/01/2007 12:23AM

Thanks!

Hi,

Normally you would be formatting your date on the UI or Report side, which means you must let your program display it correctly or let your reporting engine format your date as you want to.

But if you really want to change the format of your date, you can do this by changing the type to string by using the CONVERT function. The closest that I can come up with is this format:

mm/dd/yyyy

checki it here (code 101):

http://msdn2.microsoft.com/en-us/library/ms187928.aspx

the syntax would be like this in SQL

SELECT CONVERT('your date', nvarchar(MAX), 101)

Maybe you can mix it up with code 108 so that you can concatenate the time with it.

cheers,

Paul June A. Domag

|||

The reason I am formatting it in SQL is this is inside a trigger written in pure SQL which emails from the DB.

Yeah CONVERT() using type 101 was the closest I could get as well, but it only displays date, no time. I need both date and time in the format I specified above.

This is absolutley stupid that they did it this way, they should take a lession from MySQL which allows you to format a DATETIME in any fashion via strings such as %m/%d/%Y etc, etc.

Anybody have other ideas?

|||

Hi,

Why not just combine the codes 101 and 108 and maybe manually parse it using substring?

You can create a scalar function to make it reusable.

Code Snippet

DECLARE @.dt VARCHAR(MAX)

SELECT @.dt = CONVERT(nvarchar(MAX), GETDATE(), 101) + ' ' + CONVERT(nvarchar(MAX), GETDATE(), 108)

-- After this just use Substring to satisfy your formatting

note: varchar(max) is only available in SQL2005. specify the lenght if your using SQL2000

cheers,

Paul June A. Domag

|||

As Paul indicated, formating is normally left to the client application. I suspect the developer you are working with either does not know how to properly format for display in his/her application, or is too lazy and is passing the responsibility off to the database.

This expression should provide the date in the form you desire. Replace the [ @.MyDate ] with your column or date value. You can easily create your own function that will do this for you so that you can re-use this expression.

DECLARE @.MyDate datetime

SET @.MyDate = '2007/07/21 11:35:45.255PM'

SELECT MyDate =
convert( varchar(10), @.MyDate, 101) +
stuff( right( convert( varchar(26), @.MyDate, 109 ), 15 ), 7, 7, ' ' )

MyDate
-
07/21/2007 11:35 PM

|||

Arnie Rowland ,

You are the man, that worked like a charm. I guess my problem with the convert function is that there is no predefined format of:

mm/dd/yyyy hh:mm am/pm

I would assume this is very very popular, so I am confused as to why it is not implemented. As far as this benig done on the front end, this has to be done at the DB level, since we send emails out via the database.

|||

I have to say that I wouldn't want to send an email from a trigger that required any kind of special formatting. Perhaps an alert to a sysadmin, but if I was going to send correspondence like that, I would put my information in a queue of some sort and have a tool to send the email.

the CONVERT thing is a mess because it doesn't give you enough formats, unlike a proper data presentation layer would. I have (in the past) used datePart to build up a date formatter of my own, or you could probably do it with the CLR quite nicely. But SQL Server should be used to manage and manipulate data, not format it (as a broad rule of course. We all do it from time to time to appease a user/manager/programmer etc, so don't think I am saying it is horrible, it just isn't as ideal as using a programming tool made to do such things.)

Dates in a SQL Command

hi,

i have to return all the dates in a date interval, but they are not stored in a table. what i need is to calculate all the dates between 2 given dates.

is it possible to do this in a SQL command ?

thanks,

levogiro.

levogiro:

One way to do this is to loop through a table of numbers. Here the table "small_iterator" is simply a table of integers 1-32767:

declare @.dayDiff integer
declare @.firstDate datetime set @.firstDate = '12/1/6'
declare @.secondDate datetime set @.secondDate = '1/3/7'

select @.dayDiff = datediff (day, @.firstDate, @.secondDate)
--select @.dayDiff as [@.dayDiff]

select convert (varchar (10), @.firstDate + iter - 1, 101) as rangeDate
from small_iterator (nolock)
where iter <= @.dayDiff + 1

-- Sample Output:

-- rangeDate
-- -
-- 12/01/2006
-- 12/02/2006
-- ...
-- 01/03/2007

Another solution is to use a calendar table as described in this article:

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

This article describes the use of a "Numbers" table:

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html

I hope this helps.

Dave

DateName always return DatePart!

Hi all,
I have a Japanese SQL2K server that uses the following servertime format
returned by getdate() - 2005-11-23 23:29.
The problem i'm experiencing is when I use the DATENAME function, it returns
me the INT value for November (11) and not the name. Very weird. It works
correctly on my English server and i've never had this problem before using
DATENAME.
The selects are below:
SELECT DATEPART(month, GETDATE()) -- returns 11 - Correct
SELECT DATENAME(month,GETDATE()) -- returns 11 - Incorrect
Any help would be appreciated.
Regards
ImmyHi, Immy
The values returned by the DATENAME function can be in found the
syslanguages table (in the master database). Besides Japanese, this
problem also affects Korean and Chinese languages.
Razvan|||I know where I can find them , but that doesn't help the problem I am
having.
Simply, the DATENAME function should return the Name of the Day, Month
etc... but I always get the datepart int returned.
The syslanguages table on the "good" and "bad" server are exactly the same,
yet 1 server behaves correctly and the other as above!?
very !!!
Immy
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1132756544.307907.136100@.g47g2000cwa.googlegroups.com...
> Hi, Immy
> The values returned by the DATENAME function can be in found the
> syslanguages table (in the master database). Besides Japanese, this
> problem also affects Korean and Chinese languages.
> Razvan
>|||What do you want SQL Server to return ? "November" or the name of the
eleventh month in Japanese ?
If you want it to return "November", you can use one of the following
methods:
1. issue a "SET LANGUAGE ENGLISH" before executing your query
2. change the connection string to include "Current Language=English"
3. change the default language of the server, using one of the
following methods:
a) execute "EXEC sp_configure 'default language', 'English'
RECONFIGURE"
b) using Enterprise Manager, right click on your server, Properties /
Server Settings / Default language for user.
Razvan|||> 3. change the default language of the server, using one of the
> following methods:
> a) execute "EXEC sp_configure 'default language', 'English'
> RECONFIGURE"
> b) using Enterprise Manager, right click on your server, Properties /
> Server Settings / Default language for user.
Note that this setting will only affect the default language for new logins,
not for existing
logins.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1132758818.247140.278630@.g49g2000cwa.googlegroups.com...
> What do you want SQL Server to return ? "November" or the name of the
> eleventh month in Japanese ?
> If you want it to return "November", you can use one of the following
> methods:
> 1. issue a "SET LANGUAGE ENGLISH" before executing your query
> 2. change the connection string to include "Current Language=English"
> 3. change the default language of the server, using one of the
> following methods:
> a) execute "EXEC sp_configure 'default language', 'English'
> RECONFIGURE"
> b) using Enterprise Manager, right click on your server, Properties /
> Server Settings / Default language for user.
> Razvan
>|||"Immy" <imtiaz_ullah@.hotmail.com> wrote in message
news:u7HgJwD8FHA.3760@.TK2MSFTNGP14.phx.gbl...
>I know where I can find them , but that doesn't help the problem I am
>having.
> Simply, the DATENAME function should return the Name of the Day, Month
> etc... but I always get the datepart int returned.
> The syslanguages table on the "good" and "bad" server are exactly the
> same, yet 1 server behaves correctly and the other as above!?
> very !!!
> Immy
>
Hi Immy,
May I make a suggestion.
Our websites support both French and English but we have never used SQL
server to return the name of the month or w day to the client.
SQL returns the 1-12 for months and 1-7 for days and we client side
functions to display the names in the language of choice.
When you say:
"It works correctly on my English server and i've never had this problem
before using DATENAME."
Do you mean it returns the correct English name or Japanese?
If you mean English and that server does not return the correct Japanese
name, then I suggest that you look at the sysLanguage table as Razvan has
suggested. You'll see that for Japanese, the language names are listed as
1,2,3,4,5... instead of the actual names.
I don't know the reason why.|||Tibor Karaszi wrote:
> Note that this setting will only affect the default language for new login
s,
> not for existing logins.
You are correct. I should have mentioned another method:
4. change the default language of the login used by your application,
using one of the following methods:
a) execute: EXEC sp_defaultlanguage 'YourLoginName', 'English'
b) using Enterprise Manager, go to "Security" / "Logins", double click
on your login, and change the "Language" setting under "General" /
"Defaults".
Razvan

DATEDIFF Return Monday - Friday or Just weekdays

I have a query and am trying to just return the difference between two dates but not include weekends.

For instance, if I have 08/21/2006 - 08/28/2006, there are 6 weekdays.

I tried this, but I am getting 7 as a result.

SELECTDATEDIFF(weekday, request_start_date, request_end_date)AS days_off, request_idFROM request

Any help would be greatly appreciated.

You may find this UDF helpful

http://www.sqlservercentral.com/columnists/sjones/businessdays.asp

|||

You need DateDiff with the correct DatePart so I think you need either DayofYear or Hours so you can convert it back to days. Try the link below for details. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms189794.aspx

Datediff needs to deliver month AND days

with datediff all I can get it to return is months or a total of the
days...

so if the difference in dates is 12 months 4 days how do I adjust the
SQL to accommodate both?thrilled wrote:
> with datediff all I can get it to return is months or a total of the
> days...
> so if the difference in dates is 12 months 4 days how do I adjust the
> SQL to accommodate both?

So in February/March you want to return 1 month 2 days some years
and 1 month 1 day every fourth?

You'll need to write it yourself.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@.x.washington.edu
(replace x with u to respond)|||you're the best|||thrilled (decramer@.cox.net) writes:
> with datediff all I can get it to return is months or a total of the
> days...
> so if the difference in dates is 12 months 4 days how do I adjust the
> SQL to accommodate both?

As DA Morgan pointed out, the question needs further clarification.
For instance, how many months + days are there from 2005-08-27 to
2005-04-03? From 2004-02-27 to 2005-09-03?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

dateDiff always return 0

I just discover the result for my query that had use the function dateDiff seems to be in-correct, no matter what's the date, the dateDiff always return a zero. Am I using it in-correctly?

select DateDiff(mm,11/1/2004, 12/31/2005)

SELECT component_id, component_description,
SUM(CASE DateDiff(mm,date_complete,'12/31/2004')WHEN 2 THEN component_qty ELSE 0 END) AS mm1,
SUM(CASE DateDiff(mm,date_complete,'12/31/2004')WHEN 1 THEN component_qty ELSE 0 END) AS mm2,
SUM(CASE DateDiff(mm,date_complete,'12/31/2004')WHEN 0 THEN component_qty ELSE 0 END) AS mm3,
sum(component_qty) as total
FROM view_jobcomponent
WHERE date_complete between '10/1/2004' and '12/31/2004'
GROUP BY component_id, component_description
order by component_id, component_description

select DateDiff(mm,11/1/2004, 12/31/2005) should return 0.

11/1/2004 isn't a date, it's the number 11 divided by 1 divided by 2004 which is about 15 minutes into the very first day ever (ok, more like 1/1/1900 00:15:00). and 12/31/2005 is like 15 minutes and a few seconds into the very first day, and there is no month difference between them.

select DateDiff(mm,'11/1/2004', '12/31/2005') however will give you 13.

DateDiff - Return Months in Decimal?

I've been trying to create a function that returns the difference, in months, between two dates. The DateDiff function (m) returns an integer, but I really need a decimal. I could return the days instead, but it would be difficult to figure out how the number of months from this, especially when the dates span multiple years and I need to adjust for leap year. Does anyone know of a resource that might have a solution for this?

Thanks,
RobI guess you're looking for the decimal because you want what percentage of the month?

Do you need to know the number of days in that month?

It's not really a straight forward kind of thing.

I mean is this really 2 months?

SELECT DATEDIFF(m,'6/30/2004','8/31/2004')

Or this one being 1?

SELECT DATEDIFF(m,'6/30/2004','7/1/2004')|||A value of 1.5 days has meaning since it always comes out to 36 hours or x minutes or whatever. A value of 1.5 months has no meaning since months are not all the same length. 1.5 Februarys has less days than 1.5 Marchs.

Or this one being 1?

SELECT DATEDIFF(m,'6/30/2004','7/1/2004')

Seems like it if I'm at work... :)|||You could do years with a decimal...

check out the bio

http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=7198|||.695890

Cool... I'll use that formula next time I have a government form to fill out. Drive them nuts for a change.

Only problem is that years don't have 365 days in them. Actually around 365.25 I think. Hence the 'makeup' day in leap years. Since time is a human concept, why not just make every month 30 days. Year end would see us 4 days short but Dec 31st we could make 120 hours long vs. 24. That would keep the calendar in sync with the seasons. Unfortunately though, Jan 1st, at only 24 hours would not have enough time to recover from the 120 hour hangover... :)|||.695890

Cool... I'll use that formula next time I have a government form to fill out. Drive them nuts for a change.

Only problem is that years don't have 365 days in them. Actually around 365.25 I think. Hence the 'makeup' day in leap years. Since time is a human concept, why not just make every month 30 days. Year end would see us 4 days short but Dec 31st we could make 120 hours long vs. 24. That would keep the calendar in sync with the seasons. Unfortunately though, Jan 1st, at only 24 hours would not have enough time to recover from the 120 hour hangover... :)

That's funny....|||Actually, the solar year is pretty close to 365.2425 terrestrial days. So we just... Oh nevermind, I've been down this road WAY too many times now!

-PatP|||Unfortunately though, Jan 1st, at only 24 hours would not have enough time to recover from the 120 hour hangover... :)You can recover from a hangover ?!?! We just figured it would never end, so we never allow the hangover to start!

-PatP|||A pre-req for most DBA positions for sure. Listening to end users gripe for 8 hours a day used to be painful. Since I took up drinking I can now blissfully tune them out. Boss came to me years ago and said he felt my drinking was interferring with my work. I replied that my work was interferring with my drinking and quit on the spot. Never looked back. Next round's on me!

datediff (today - date)

Hello

I want to return the number of days between a date in the database and today

something like

SELECT user.fName,
user.lName & " (" & (datediff(now - user.lastVisit)) & " )"
FROM user


I must return

John Turner (38)

where 38 are the days between last visit and now

thank yousee this,now I think you can do it easily

declare @.datevar datetime
select @.datevar = getdate()


*Example for datediff : getting no of days passed since 01-01-2006*/

select datediff(dd,'20060101',@.datevar) [No of days since 01-01-2006]|||it works !

thanks a lot