how to get day of week in sql ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2005 Forums

Transact-SQL (2005)

how to get day of week in sql ?

Author

Topic

hieukenpro
Starting Member

5 Posts

Posted - 2011-06-12 : 13:25:06

Hello, i have customer table (include ID,Name,Birthday.....)
i want to get customer information with condition is birthday of customer in current week ,how to write the query ?

Thank,
Trung Hi?u

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2011-06-12 : 13:49:35

Could you post the DDL of the table , with data types etc

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

hieukenpro
Starting Member

5 Posts

Posted - 2011-06-12 : 14:13:58

create table customer
(
id int identity primary key,
cus_name nvarchar(50),
cus_birthdate datetime,
cus_phone char(10)
)

Thanks,
Trung Hieu

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-12 : 14:35:15

This depends on your definition of a week. If your week starts on Sunday, you can write the query like this:


select
   *,
   case 
      when dateadd(dd,-1,cus_birthdate)/7 = dateadd(dd,-1,getdate())/7 then 1 
      else 0 
   end BirthDayIsInCurrentWeek
from
   customer

Otherwise, for example, if your week starts on Monday, change the when expression to


when dateadd(dd,0,cus_birthdate)/7 = dateadd(dd,0,getdate())/7 then 1

Of course, if you just want to get the customers whose birthday is in current week, use the same case expression in the where clause as in


where
   dateadd(dd,-1,cus_birthdate)/7 = dateadd(dd,-1,getdate())/7

hieukenpro
Starting Member

5 Posts

Posted - 2011-06-12 : 15:30:23

you can't use dateadd(dd,-1,cus_birthdate)/7 because have a error

Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

Thanks,
Trung Hieu

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-12 : 15:50:49

Ugh! Sorry about that. I promise, I will never again post code before testing it myself!!!


select
   *,
   case 
      when datediff(dd,-1,cus_birthdate)/7 = datediff(dd,-1,getdate())/7 then 1 
      else 0 
   end BirthDayIsInCurrentWeek
from
   customer

hieukenpro
Starting Member

5 Posts

Posted - 2011-06-12 : 17:07:06

I have infomation about birthday customer that's 1991-06-13 00:00:00.000,but BirthDayIsInCurrentWeek column still return 0

Thanks,
Trung Hieu

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-12 : 17:33:48

What I posted only checks if the cust_birthdate is in the same week as the current date. Babies less than 7 days old can't possibly be customers, can they? This fixes that to look for just the dates rather than year and date.


select
   *,
   case 
      when datediff(dd,-1,dateadd(yy,year(getdate())-year(cus_birthdate),cus_birthdate))/7 = datediff(dd,-1,getdate())/7 then 1 
      else 0 
   end BirthDayIsInCurrentWeek
from
   customer

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-06-13 : 00:25:45

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80709

N 56°04'39.26"
E 12°55'05.63"

hieukenpro
Starting Member

5 Posts

Posted - 2011-06-13 : 12:23:16

Thanks everybody, i have done

Trung Hieu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources