? DatePart in SQL= Select statement 2. Set first day of week

[Cheryl]

I am having a couple of problems that I would be grateful for assistance with:


1. First of all,

I am having trouble with my syntax when trying to retrieve the week number
in Frontpage connection to SQL 2000 database.

I can connect successfully.. and retrieve information using a straight
forward approach...

I want a drop down that consists of a distinct week number extracted from
the Date called DateWorked so that a user can select Week 40 and see all the
work done for that week..

strSQL2 = "Select DatePart(ww,DateWorked) AS WeekNo, DateWorked FROM
TimeSheets ORDER BY WeekNo ASC"

Second Problem:

2. I am not sure how to code it to make the first day of the work week a
Saturday.. We run Saturday - Friday....

This is going to be done from Frontpage 2003 and connecting to an sql 2000
database.

Thank you in advance for any direction you can give me...

 

[Stefan B Rusynko]

DatePart(interval, date[, firstdayofweek[, firstweekofyear]])
firstdayof week is Optional. If not specified, Sunday is assumed.
vbSaturday or 7 = Saturday
firstweekofyear Optional. If not specified, the first week is assumed to be the week in which January 1 occurs.





| I am having a couple of problems that I would be grateful for assistance with:
|
|
| 1. First of all,
|
| I am having trouble with my syntax when trying to retrieve the week number
| in Frontpage connection to SQL 2000 database.
|
| I can connect successfully.. and retrieve information using a straight
| forward approach...
|
| I want a drop down that consists of a distinct week number extracted from
| the Date called DateWorked so that a user can select Week 40 and see all the
| work done for that week..
|
| strSQL2 = "Select DatePart(ww,DateWorked) AS WeekNo, DateWorked FROM
| TimeSheets ORDER BY WeekNo ASC"
|
| Second Problem:
|
| 2. I am not sure how to code it to make the first day of the work week a
| Saturday.. We run Saturday - Friday....
|
| This is going to be done from Frontpage 2003 and connecting to an sql 2000
| database.
|
| Thank you in advance for any direction you can give me...
|
|

 

[Jon Spivey]

That's datepart in VB Script - the syntax is different in T-SQL. To set the
first day of the week we use datefirst, 1=Monday, 2=Tuesday etc. So to have
Saturday as the first day of the week

SET DateFirst 6 Select DatePart(ww,DateWorked) etc

 

[Cheryl]

Thank you both.. I will give that a try...

I resolved the first issue... so this is my 2nd hurdle..

That's datepart in VB Script - the syntax is different in T-SQL. To set the
first day of the week we use datefirst, 1=Monday, 2=Tuesday etc. So to have
Saturday as the first day of the week

SET DateFirst 6 Select DatePart(ww,DateWorked) etc


--
Cheers,
Jon
Microsoft MVP

DatePart(interval, date[, firstdayofweek[, firstweekofyear]])
firstdayof week is Optional. If not specified, Sunday is assumed.
vbSaturday or 7 = Saturday
firstweekofyear Optional. If not specified, the first week is assumed to
be the week in which January 1 occurs.





| I am having a couple of problems that I would be grateful for assistance
with:
|
|
| 1. First of all,
|
| I am having trouble with my syntax when trying to retrieve the week
number
| in Frontpage connection to SQL 2000 database.
|
| I can connect successfully.. and retrieve information using a straight
| forward approach...
|
| I want a drop down that consists of a distinct week number extracted
from
| the Date called DateWorked so that a user can select Week 40 and see all
the
| work done for that week..
|
| strSQL2 = "Select DatePart(ww,DateWorked) AS WeekNo, DateWorked FROM
| TimeSheets ORDER BY WeekNo ASC"
|
| Second Problem:
|
| 2. I am not sure how to code it to make the first day of the work week a
| Saturday.. We run Saturday - Friday....
|
| This is going to be done from Frontpage 2003 and connecting to an sql
2000
| database.
|
| Thank you in advance for any direction you can give me...
|
|