Day of the week

Hi group ,
Please let me know which formule is usefull for calculate the day of week
based in a date.
my date is datetime.
day = convert(int,MyDate) % 7
is't working
Look up DATENAME & DATEPART functions in SQL Server Books Online.
Anith
|||SELECT DATENAME(dw, getdate())
|||But I need the number of the week. thanks you anyway.
Best regard
MArio
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:BBA8C3BE-0086-49B4-B109-843FEEBED93D@.microsoft.com...
> SELECT DATENAME(dw, getdate())
|||? number of the week, or day of the week? Your message and subject don't
agree.
In any case, as Anith suggested, look at DATENAME and DATEPART in Books
Online.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:#gi0eLENEHA.2640@.TK2MSFTNGP12.phx.gbl...
> But I need the number of the week. thanks you anyway.
|||I like this formula:
(@.@.DATEFIRST + DATEPART(dw, date) ) % 7
It is always
0 on Sunday
1 on Monday
2 on Monday
up to
6 on Friday

Day of the week

Hi group ,
Please let me know which formule is usefull for calculate the day of week
based in a date.
my date is datetime.
day = convert(int,MyDate) % 7
is't workingLook up DATENAME & DATEPART functions in SQL Server Books Online.
--
Anith|||But I need the number of the week. thanks you anyway.
Best regard
MArio
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:BBA8C3BE-0086-49B4-B109-843FEEBED93D@.microsoft.com...
> SELECT DATENAME(dw, getdate())|||? number of the week, or day of the week? Your message and subject don't
agree.
In any case, as Anith suggested, look at DATENAME and DATEPART in Books
Online.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:#gi0eLENEHA.2640@.TK2MSFTNGP12.phx.gbl...
> But I need the number of the week. thanks you anyway.sql

Day of the week

Hi group ,
Please let me know which formule is usefull for calculate the day of week
based in a date.
my date is datetime.
day = convert(int,MyDate) % 7
is't workingLook up DATENAME & DATEPART functions in SQL Server Books Online.
Anith|||SELECT DATENAME(dw, getdate())|||But I need the number of the week. thanks you anyway.
Best regard
MArio
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:BBA8C3BE-0086-49B4-B109-843FEEBED93D@.microsoft.com...
> SELECT DATENAME(dw, getdate())|||? number of the week, or day of the week? Your message and subject don't
agree.
In any case, as Anith suggested, look at DATENAME and DATEPART in Books
Online.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:#gi0eLENEHA.2640@.TK2MSFTNGP12.phx.gbl...
> But I need the number of the week. thanks you anyway.|||I like this formula:
(@.@.DATEFIRST + DATEPART(dw, date) ) % 7
It is always
0 on Sunday
1 on Monday
2 on Monday
up to
6 on Friday

DateTime types and getdate() comparison

SQL 2000. Let's say column MyDate is a datetime type. Is this
comparison syntax OK as is?
... where MyDate <= getdate()
Or is some formatting of the column value and/or of the function's
return value required for the comparison to work?
Thanks
LiamComparison operators (<,>,=, <>, >=, <= ) are allowed between two values wit
h
a datatype of datetime. Your expression is fine.
However, if you want to do things like add or subtract datetime values, you
will need to use the date and time functions in SQL Server.
"Liam" wrote:

> SQL 2000. Let's say column MyDate is a datetime type. Is this
> comparison syntax OK as is?
> .... where MyDate <= getdate()
> Or is some formatting of the column value and/or of the function's
> return value required for the comparison to work?
> Thanks
> Liam
>|||depends on what you need
but don't convert the column - you'll lose any sargability if it's indexed.
i tend not to try to rely on date data having being inserted with a time
of midnight, so i convert the variable and perform range queries
if you need mydate <= just the date: then do
MyDate < tomorrow at midnight
e.g.
where MyDate < dateadd(day, datediff(day, 0, getdate()), 0)+1
or if you need MyDate for just today
where MyDate >= dateadd(day, datediff(day, 0, getdate()), 0)
and MyDate < dateadd(day, datediff(day, 0, getdate()), 0)+1
or if you need mydate <= current date and time, then simply using
getdate() is appropriate.
Liam wrote:
> SQL 2000. Let's say column MyDate is a datetime type. Is this
> comparison syntax OK as is?
> ... where MyDate <= getdate()
> Or is some formatting of the column value and/or of the function's
> return value required for the comparison to work?
> Thanks
> Liam

datetime function with no time component?

Hi All,

When I compare dates but I want to ignore the time within the datetime I find myself doing this:

CONVERT(int, CONVERT(char(8), @.MyDate, 112))

style 112 is yyyymmdd

int is very predictable for comparisons, and performs well too.

It works but it is not readable, especially if you have several of these expressions in the same WHERE clause or CASE stmt. I also tried a udf but that has its own reusability problems across dbs and projects.

Is there a cleaner way to do this with a system function?

Carl

If you just want to compare dates, ignoring times, you could use the datediff function:

WHERE datediff( day, MyFirstDate, MyOtherDateTime ) = 0

For example:

Code Snippet

SELECT
Match = CASE
WHEN datediff( day, '2007/07/07 08:45 AM', getdate() ) = 0
THEN 'Match -Same Day'
ELSE 'Bummer! -No Match'
END,
NoMatch = CASE
WHEN datediff( day, '2007/07/06 08:45 AM', getdate() ) = 0
THEN 'Same Day'
ELSE 'Different Day'
END

Match NoMatch
-- -
Match -Same Day Different Day

DATEDIFF(), using the 'day' parameter, verifies that the two values are the same date IF there is NO difference [ = 0 ].

|||

Thanks Arnie,

For = and != logic, this is cleaner.

Not much of an improvement in readability for >, < , !>, and !< type comparisons

Carl

|||

And not too good for performance either.

While using the datediff() process 'looks' good, or as you said, 'cleaner', performance, related to other methods, can be disasterous. It will require at 'best', a clustered index scan. Actually, unless there is an index on the datetime column, it has to scan the entire table -which is what a 'clustered index scan' really is.

Compare that with the second option, my preferred method, of using date values in the criteria.

Code Snippet

USE Northwind
GO

SELECT *
FROM Orders
WHERE datediff( day, OrderDate, '1996/08/27' ) = 0

SELECT *
FROM Orders
WHERE ( OrderDate >= '1996/08/27'
AND OrderDate < '1996/08/28'
)

If you examine the execution plans, you will notice the method using the datediff() takes 19 times as long to execute since it has to scan the entire table.

DateDiff rewrite

Given this query, how can I rewrite it to make it more indexable:
SELECT tableUID FROM MyTable
WHERE DateDiff(D, mydate, GETDAT()) BETWEEN 0 AND 29
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1Try this
SELECT tableUID FROM MyTable
WHERE mydate BETWEEN DATEADD(d,-29,GETDATE()) and GETDATE()
Denis the SQL Menace
http://sqlservercode.blogspot.com/
cbrichards via droptable.com wrote:
> Given this query, how can I rewrite it to make it more indexable:
> SELECT tableUID FROM MyTable
> WHERE DateDiff(D, mydate, GETDAT()) BETWEEN 0 AND 29
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200608/1

DateDiff rewrite

Given this query, how can I rewrite it to make it more indexable:
SELECT tableUID FROM MyTable
WHERE DateDiff(D, mydate, GETDAT()) BETWEEN 0 AND 29
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1Try this
SELECT tableUID FROM MyTable
WHERE mydate BETWEEN DATEADD(d,-29,GETDATE()) and GETDATE()
Denis the SQL Menace
http://sqlservercode.blogspot.com/
cbrichards via SQLMonster.com wrote:
> Given this query, how can I rewrite it to make it more indexable:
> SELECT tableUID FROM MyTable
> WHERE DateDiff(D, mydate, GETDAT()) BETWEEN 0 AND 29
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1