how can i query the date one week back from now?

for example today is May,2 2006: my query should return all data where date = April 24, 2006.

Try this RDL expression for calculating the previous week:
=Today.AddDays(-7)

If you want to do this directly in the query, you will need to look for date related functions for the particular database you are working with. For SQL Server queries you would use the DateAdd function: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_3vtw.asp

-- Robert

|||thanks a lot!!! sql

how can I put this into one query

I have a table (tblJobs) that has a delivery date - to this date I want to
add a set amount of 'working' days
I have a table of dates with columns that specify whether the date is a
wday and a holiday.
for each record in the tblJobs table I want the calculated date to appear in
the record.
Eg
tblJobs
JobNo DeliveryDate
G123 03/01/2006
tblHols
dt isWday isHoliday
01/01/2006 0 1
02/01/2006 1 1
03/01/2006 1 0
04/01/2006 1 0
05/01/2006 1 0
06/01/2006 1 0
07/01/2006 0 0
08/01/2006 0 0
09/01/2006 1 0
10/01/2006 1 1
11/01/2006 1 0
ResultSet
Job DeliveryDate CalcDate (i.e. DeliveryDate + 5
WorkingDays)
G123 03/01/2006 11/01/2006
I have the query to calculate the date but I don't know how to pass the
DeliveryDate for each record to the subquery
Select *,(SELECT c.dt
FROM dbo.tblHols c
WHERE
c.isWday = 1
AND c.isHoliday =0
AND c.dt > @.dte
AND c.dt <= DATEADD(day, 25, @.dte) = = = @.dte needs to equal
DeliveryDate for each record
AND 5 = (
SELECT COUNT(*)
FROM dbo.tblHols c2
WHERE c2.dt >= @.dte
AND c2.dt <= c.dt
AND c2.isWday=1
AND c2.isHoliday=0
)
) as CalcDate
FROM tblJobs
Here are the scripts to create the tables etc
CREATE TABLE [dbo].[tblHols] (
[dt] [datetime] NOT NULL ,
[isHoliday] [bit] NULL ,
[isWday] [bit] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblHols] ADD
CONSTRAINT [PK_tblHols] PRIMARY KEY CLUSTERED
(
[dt]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblJobs] (
[Job] [nvarchar] (8) COLLATE Latin1_General_BIN NOT NULL ,
[DeliveryDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblJobs] ADD
CONSTRAINT [PK_tblJobs] PRIMARY KEY CLUSTERED
(
[Job]
) ON [PRIMARY]
GO
SET NOCOUNT ON
DECLARE @.dt SMALLDATETIME
SET @.dt = '20060101'
WHILE @.dt < '20070101'
BEGIN
INSERT dbo.tblHols(dt) SELECT @.dt
SET @.dt = @.dt + 1
END
UPDATE dbo.tblHols SET
isWday = CASE
WHEN DATEPART(DW, dt) IN (1,7)
THEN 0
ELSE 1 END,
isHoliday = 0
UPDATE tblHols
SET
isHoliday = 1
WHERE datepart(d,dt) IN (1,2,10)
INSERT INTO tblJobs ( Job, DeliveryDate )
SELECT 'G1234' AS Expr1, '20060102' AS Expr2
INSERT INTO tblJobs ( Job, DeliveryDate )
SELECT 'G2234' AS Expr1, '20060105' AS Expr2On Sat, 17 Dec 2005 23:14:16 -0000, Al Newbie wrote:
(snip)
>I have the query to calculate the date but I don't know how to pass the
>DeliveryDate for each record to the subquery
>Select *,(SELECT c.dt
> FROM dbo.tblHols c
> WHERE
> c.isWday = 1
> AND c.isHoliday =0
> AND c.dt > @.dte
> AND c.dt <= DATEADD(day, 25, @.dte) = = = @.dte needs to equal
>DeliveryDate for each record
> AND 5 = (
> SELECT COUNT(*)
> FROM dbo.tblHols c2
> WHERE c2.dt >= @.dte
> AND c2.dt <= c.dt
> AND c2.isWday=1
> AND c2.isHoliday=0
> )
> ) as CalcDate
>FROM tblJobs
Hi Al,
Not sure if you still need this after my previous reply, but you can
simply replace "@.dte" with "tblJobs.DeliveryDate".
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||i think it is better to be later than to be never :)
SET NOCOUNT ON;
SET ANSI_NULLS ON;
USE YOUR_DB;
IF EXISTS(SELECT * FROM YOUR_DB.INFORMATION_SCHEMA.TABLES
WHERE table_name='Jobs') DROP TABLE Jobs;
IF EXISTS(SELECT * FROM YOUR_DB.INFORMATION_SCHEMA.TABLES
WHERE table_name='Calendar') DROP TABLE Calendar;
CREATE TABLE Jobs(
job_id CHAR(4) NOT NULL PRIMARY KEY,
dlvr_dt DATETIME NOT NULL);
INSERT INTO Jobs VALUES('G123', '2006-01-03');
INSERT INTO Jobs VALUES('G234', '2005-01-01');
CREATE TABLE Calendar( -- Hols(
cal_dt DATETIME NOT NULL PRIMARY KEY,
is_wday INTEGER NOT NULL CHECK(is_wday IN(0,1)),
is_hday INTEGER NOT NULL CHECK(is_hday IN(0,1)))
INSERT INTO Calendar
SELECT '2006-01-01', 0, 1 UNION ALL
SELECT '2006-01-02', 1, 1 UNION ALL
SELECT '2006-01-03', 1, 0 UNION ALL
SELECT '2006-01-04', 1, 0 UNION ALL
SELECT '2006-01-05', 1, 0 UNION ALL
SELECT '2006-01-06', 1, 0 UNION ALL
SELECT '2006-01-07', 0, 0 UNION ALL
SELECT '2006-01-08', 0, 0 UNION ALL
SELECT '2006-01-09', 1, 0 UNION ALL
SELECT '2006-01-10', 1, 1 UNION ALL
SELECT '2006-01-11', 1, 0;
SELECT J.job_id, J.dlvr_dt, C.cal_dt
FROM Jobs as J, Calendar as C
WHERE C.cal_dt >= J.dlvr_dt
AND C.is_wday = 1 AND C.is_hday = 0
AND 6 = (SELECT COUNT(*)
FROM Calendar as C2
WHERE C2.cal_dt between J.dlvr_dt AND C.cal_dt
AND C2.is_wday = 1 AND C2.is_hday = 0)

how can i insert, retrieve date from ASPX page in table ?

hello all

pls tell me

how can i insert, retrieve date from ASPX(vs2003) page in table in sql server 2000??

i m in trouble

pls help me

Please do not use the ASP.NET Forums to get others to do your work for you. The questions you have been asking show that you are not eventrying to work it out yourself.

What you need to do is useGoogle to research the topic. Then, using that research, have a go at writing the code you need. If that code fails, and you have spent more than ten minutes trying to fix it yourself, show us the code and explain what you are trying to do and how it is failing.

You should also considering buying a good book with which to learn ASP.NET: ASP.NETUnleashed, Second Edition.

How can I insert Date in SQL Server 2000 database (table )from ASP.NET 1.1. Program?

hi ALL !!!

How can I insert Date in SQL Server 2000 database(table ) from ASP.NET 1.1. Program??

pls sendme codeif u can

pls help me ..

Well w/out seeing how you've been trying to insert it--you just take whatever control your taking a date from and do CDate(txtbox1.text) --if this doesn't help, lets see some of your code|||

You could usehttp://www.sqldatalayer.com to create your DataLayer.

I am the author of the above mentioned tool, and I'm currently working on the help documentation.

I plan on having fully functionaly example apps, in the help as well.

How can I get the last login date for a login ??

How can I get the last login date for a login '
Thx
JohnnyThis information is not tracked by default. You could capture it using SQL
Profiler and output the information to a SQL table, or create your own
triggers on user tables to track this.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Take a look at the login_time column of
master.dbo.sysprocesses.
Linchi

quote:

---

>--Original Message--
>How can I get the last login date for a login '
>Thx
>Johnny
>.
>

---

|||You could also set login auditing to All and query the SQL errorlog where
all the logins are recorded
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Johnny Silvestre" <johnny_silvestre@.yahoo.de> wrote in message
news:01e401c3d549$ca9d9640$a301280a@.phx.gbl...

quote:

---

> How can I get the last login date for a login '
> Thx
> Johnny

---

|||hi Johnny ,
This is not tracked by default.You have to do some settings for that. SQL
Server can log event information for logon attempts and you can view it by
reviewing the errorlog. By turning on the auditing level of SQL Server.
follow these steps to enable auditing of all/successful connections with
Enterprise Manager in SQL Server:
Expand a server group.
Right-click a server, and then click Properties.
On the Security tab, under Audit Level, click all/success etc(required
option).
You must stop and restart the server for this setting to take effect.
-- Vishal|||Oops! Thought you wanted the login date of a spid.
Linchi

quote:

---

>--Original Message--
>Take a look at the login_time column of
>master.dbo.sysprocesses.
>Linchi
>
>.
>

---

How can I format the Date in the SQL Table using a SQL query

Hi

I have a SQL table that contains date in this format :-

2006-07-02 16:20:01.000
2006-07-02 16:21:00.000
2006-07-02 16:21:01.000
2006-07-02 16:22:00.000
2006-07-02 16:22:02.000
2006-07-02 16:23:00.000

The date above contains seconds that I dont want, how can I remove those seconds so that the output looks like :-

2006-07-02 16:20:00.000
2006-07-02 16:21:00.000
2006-07-02 16:21:00.000
2006-07-02 16:22:00.000
2006-07-02 16:22:00.000
2006-07-02 16:23:00.000

Your help will be highly appreciated.

Hi,

You can do as..

update tablename set datecolumn = select dateadd(s, -datepart(s,datecolumn), datecolumn)

|||

That will change the data. If you only want to change the display, then try this ;

select replace(convert(varchar(20), columnName, 102), '.', '-') + ' ' + left(convert(varchar(20), columnName, 108), 5) + ':00.000'
from tableName

|||

In case you do not want to update the table you just need to change it in display you can do as..

select dateadd(s, -datepart(s,datecolumn), datecolumn)

from tablename

|||

Hi, thanks for the reply

Here is what i have tried

UPDATE [dbo].[Date_Test] SET Date = SELECT DATEADD(s, -DATEPART(s,Date), Date)

and Im getting the following error, i dont understand whats causing it.

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'SELECT'.

Please help.

|||

Rod Colledge wrote:

That will change the data. If you only want to change the display, then try this ;

select replace(convert(varchar(20), columnName, 102), '.', '-') + ' ' + left(convert(varchar(20), columnName, 108), 5) + ':00.000'
from tableName

Thanks, but I want to change the data, not to display it.

|||

Hi,

You need to remove Select Key word..

UPDATE [dbo].[Date_Test] SET Date = DATEADD(s, -DATEPART(s,Date), Date)

|||

Shallu wrote:

Hi,

You need to remove Select Key word..

UPDATE [dbo].[Date_Test] SET Date = DATEADD(s, -DATEPART(s,Date), Date)

Thanks Shallu, it work perfect

How can I format a datetime field in a stored procedure

How can I format a datetime field in a stored procedure to return just the date in MM/DD/YYYY format?

CONVERT(varchar, youddatecolumn, 101).

Check out the CAST and CONVERT functions in BOL for more formats.

|||

Use T-SQL Convert function and instead of returning date object return string representation of that date.

Or, return date object from Stored Procedure and convert it in C# code to appropriate format.

|||ived been properly arranging the quotes and single quotes but still it has error...it changes the error but has error still...

'2006' 付近に不適切な構文があります。 (There is an improper syntax in the vicinity ..'20 06'... )

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: '2006' 付近に不適切な構文があります。

Source Error:

Line 113: sqlcom.Parameters.AddWithValue("@.ninka_date_kyou", textbox_approval_date.Text)Line 114:Line 115: sqlcom.ExecuteNonQuery()Line 116: MyConn.Close()Line 117: sqlcom.Dispose()

MyConn.Open()
sqlcom.CommandType = CommandType.Text
sqlcom.CommandText = "update TE_zangyou Set ninka_nen_from = @.ninka_nen_from ,ninka_gatsu_from = @.ninka_gatsu_from, ninka_hi_from = @.ninka_hi_from ,ninka_nen_to = @.ninka_nen_to, ninka_gatsu_to = @.ninka_gatsu_to, ninka_hi_to = @.ninka_hi_to, ninka_ji_from = @.ninka_ji_from,ninka_ji_to = @.ninka_ji_to,ninka_bun_from = @.ninka_bun_from,ninka_bun_to = @.ninka_bun_to,ninka_day_name_from = @.ninka_day_name_from,ninka_day_name_to= @.ninka_day_name_to,ninka_date_kyou= @.ninka_date_kyou where syain_No = " + Request.QueryString("syain_No")+ " and date_kyou ='" + Request.QueryString("date_kyou") + "' and time_kyou ='" + Request.QueryString("time_kyou") + "'"

but the format in my database is like this:"2006/12/12" and its just character string...

so it will just compare whether they have thesame date_kyou...

but it produces errors like that!

can u help me determine my mistake? thank you..:-)

|||

Two things here:

1.Please use all parameters for your WHERE clause variables;

2.What data types are these columns in your database table?

Datetime column may be hard to use at the beginning, but it is worth it. Try it.

|||

Hi,

is your datafield varchar or datetime?

if it's a varchar field containing the date in a string format 'yyyy/mm/dd', it may be a syntax error.
You can try debugging your appln and providing us with the value of the data in your code to check if theres a syntax error with "textbox_approval_date.Text".

if it's a datetime field, you can try converting the value in "textbox_approval_date.Text" to a datetime object first before passing it into your statement using the CONVERT function.

Hope this helps.

|||

its only character string... and its working when i just put requerystring(syain_No) but when i included the date_kyou and time_kyou,.,,, it produces that kind of error...

what do u think is the problem here?

|||

Dim MyConn As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("StrConn").ConnectionString)
Dim sqlcom As SqlClient.SqlCommand = MyConn.CreateCommand()

MyConn.Open()
sqlcom.CommandType = CommandType.Text
sqlcom.CommandText = "update TE_zangyou Set ninka_nen_from = @.ninka_nen_from ,ninka_gatsu_from = @.ninka_gatsu_from, ninka_hi_from = @.ninka_hi_from ,ninka_nen_to = @.ninka_nen_to, ninka_gatsu_to = @.ninka_gatsu_to, ninka_hi_to = @.ninka_hi_to, ninka_ji_from = @.ninka_ji_from,ninka_ji_to = @.ninka_ji_to,ninka_bun_from = @.ninka_bun_from,ninka_bun_to = @.ninka_bun_to,ninka_day_name_from = @.ninka_day_name_from,ninka_day_name_to= @.ninka_day_name_to,ninka_date_kyou= @.ninka_date_kyou where syain_No = " + Request.QueryString("syain_No")+ " and date_kyou ='" + Request.QueryString("date_kyou") + "' and time_kyou ='" + Request.QueryString("time_kyou") + "'"

sqlcom.Parameters.AddWithValue("@.ninka_nen_from", TextBox1.Text)
sqlcom.Parameters.AddWithValue("@.ninka_gatsu_from", TextBox2.Text)
sqlcom.Parameters.AddWithValue("@.ninka_hi_from", TextBox3.Text)
sqlcom.Parameters.AddWithValue("@.ninka_day_name_from", TextBox5.Text)
sqlcom.Parameters.AddWithValue("@.ninka_ji_from", textbox_from_hr.Text)
sqlcom.Parameters.AddWithValue("@.ninka_bun_from", textbox_from_min.Text)
sqlcom.Parameters.AddWithValue("@.ninka_nen_to", TextBox6.Text)
sqlcom.Parameters.AddWithValue("@.ninka_gatsu_to", TextBox7.Text)
sqlcom.Parameters.AddWithValue("@.ninka_hi_to", TextBox8.Text)
sqlcom.Parameters.AddWithValue("@.ninka_day_name_to", TextBox9.Text)
sqlcom.Parameters.AddWithValue("@.ninka_ji_to", textbox_to_hr.Text)
sqlcom.Parameters.AddWithValue("@.ninka_bun_to", textbox_to_min.Text)
Label28.Visible = True
textbox_approval_date.Visible = True
textbox_approval_date.Text = Date.Today
statusbox_approve_mesg.Visible = True
sqlcom.Parameters.AddWithValue("@.ninka_date_kyou", textbox_approval_date.Text)

sqlcom.ExecuteNonQuery()
MyConn.Close()
sqlcom.Dispose()
MyConn.Dispose()
MsgBox(" Application Dates was Approved")

TextBox1.Enabled = False
TextBox2.Enabled = False
TextBox3.Enabled = False
TextBox6.Enabled = False
TextBox5.Enabled = False
TextBox7.Enabled = False
TextBox8.Enabled = False
TextBox9.Enabled = False
textbox_from_hr.Enabled = False
textbox_from_min.Enabled = False
textbox_to_hr.Enabled = False
textbox_to_min.Enabled = False
Reason.Enabled = False
compensantory.Enabled = False
Edit1.Visible = False
Button_save.Visible = False
Button_approve.Visible = False
Button_reject.Visible = False
Button_send.Visible = True

&&&& my code... what u think? there is an error in thebold words

|||Dim MyConn As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("StrConn").ConnectionString)
Dim sqlcom As SqlClient.SqlCommand = MyConn.CreateCommand()

MyConn.Open()
sqlcom.CommandType = CommandType.Text
sqlcom.CommandText = "update TE_zangyou Set ninka_nen_from = @.ninka_nen_from ,ninka_gatsu_from = @.ninka_gatsu_from, ninka_hi_from = @.ninka_hi_from ,ninka_nen_to = @.ninka_nen_to, ninka_gatsu_to = @.ninka_gatsu_to, ninka_hi_to = @.ninka_hi_to, ninka_ji_from = @.ninka_ji_from,ninka_ji_to = @.ninka_ji_to,ninka_bun_from = @.ninka_bun_from,ninka_bun_to = @.ninka_bun_to,ninka_day_name_from = @.ninka_day_name_from,ninka_day_name_to= @.ninka_day_name_to,ninka_date_kyou= @.ninka_date_kyou where syain_No = " + Request.QueryString("syain_No")+ " and date_kyou ='" + Request.QueryString("date_kyou") + "' and time_kyou ='" + Request.QueryString("time_kyou") + "'"

sqlcom.Parameters.AddWithValue("@.ninka_nen_from", TextBox1.Text)
sqlcom.Parameters.AddWithValue("@.ninka_gatsu_from", TextBox2.Text)
sqlcom.Parameters.AddWithValue("@.ninka_hi_from", TextBox3.Text)
sqlcom.Parameters.AddWithValue("@.ninka_day_name_from", TextBox5.Text)
sqlcom.Parameters.AddWithValue("@.ninka_ji_from", textbox_from_hr.Text)
sqlcom.Parameters.AddWithValue("@.ninka_bun_from", textbox_from_min.Text)
sqlcom.Parameters.AddWithValue("@.ninka_nen_to", TextBox6.Text)
sqlcom.Parameters.AddWithValue("@.ninka_gatsu_to", TextBox7.Text)
sqlcom.Parameters.AddWithValue("@.ninka_hi_to", TextBox8.Text)
sqlcom.Parameters.AddWithValue("@.ninka_day_name_to", TextBox9.Text)
sqlcom.Parameters.AddWithValue("@.ninka_ji_to", textbox_to_hr.Text)
sqlcom.Parameters.AddWithValue("@.ninka_bun_to", textbox_to_min.Text)
Label28.Visible = True
textbox_approval_date.Visible = True
textbox_approval_date.Text = Date.Today
statusbox_approve_mesg.Visible = True
sqlcom.Parameters.AddWithValue("@.ninka_date_kyou", textbox_approval_date.Text)

sqlcom.ExecuteNonQuery()
MyConn.Close()
sqlcom.Dispose()
MyConn.Dispose()
MsgBox(" Application Dates was Approved")

TextBox1.Enabled = False
TextBox2.Enabled = False
TextBox3.Enabled = False
TextBox6.Enabled = False
TextBox5.Enabled = False
TextBox7.Enabled = False
TextBox8.Enabled = False
TextBox9.Enabled = False
textbox_from_hr.Enabled = False
textbox_from_min.Enabled = False
textbox_to_hr.Enabled = False
textbox_to_min.Enabled = False
Reason.Enabled = False
compensantory.Enabled = False
Edit1.Visible = False
Button_save.Visible = False
Button_approve.Visible = False
Button_reject.Visible = False
Button_send.Visible = True

&&&& my code... what u think? there is an error in thebold words

and date_kyou and time_kyou are character string only...

|||

Change to this and try:

...

where syain_No =@.syain_No AND date_kyou=@.date_kyou AND time_kyou=@.time_kyou

.....

sqlcom.Parameters.AddWithValue("@.syain_No", Request.QueryString("syain_No"))

sqlcom.Parameters.AddWithValue("@.date_kyou", Request.QueryString("date_kyou"))

sqlcom.Parameters.AddWithValue("@.time_kyou,Request.QueryString("time_kyou"))

|||

パラメータ化クエリ '(@.syain_No nvarchar(4),@.date_kyou nvarchar(12),@.time_kyou nvarch' にはパラメータ @.time_kyou が必要ですが、指定されていません。 (It is not specified though parameter @.time_kyou is necessary for (@.syain_No nvarchar(4), @.date_kyou nvarchar(12), and @.time_kyou nvarch Ceri of making to the parameter ''. )

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: パラメータ化クエリ '(@.syain_No nvarchar(4),@.date_kyou nvarchar(12),@.time_kyou nvarch' にはパラメータ @.time_kyou が必要ですが、指定されていません。

Source Error:

Line 117: sqlcom.Parameters.AddWithValue("@.ninka_date_kyou", textbox_approval_date.Text)Line 118:Line 119: sqlcom.ExecuteNonQuery()Line 120: MyConn.Close()Line 121: sqlcom.Dispose()

Source File:C:\Documents and Settings\mspitc5\My Documents\Visual Studio 2005\MSPITC_project\overtime_application_approval_inputt.aspx.vb Line:119

Stack Trace:

&&&

it produces different error...

|||

Hi,

can you check if all the parameters are created and input with valid values?
if it doesn't solve the problem, would it be possible to translate the japanese words in your error statements as not many of us here understands it...
but i think you can narrow down the problem to the parameter @.time_kyou

|||Dim MyConn As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("StrConn").ConnectionString)
Dim sqlcom As SqlClient.SqlCommand = MyConn.CreateCommand()

MyConn.Open()
sqlcom.CommandType = CommandType.Text
sqlcom.CommandText = "update TE_zangyou Set ninka_nen_from = @.ninka_nen_from ,ninka_gatsu_from = @.ninka_gatsu_from, ninka_hi_from = @.ninka_hi_from ,ninka_nen_to = @.ninka_nen_to, ninka_gatsu_to = @.ninka_gatsu_to, ninka_hi_to = @.ninka_hi_to, ninka_ji_from = @.ninka_ji_from,ninka_ji_to = @.ninka_ji_to,ninka_bun_from = @.ninka_bun_from,ninka_bun_to = @.ninka_bun_to,ninka_day_name_from = @.ninka_day_name_from,ninka_day_name_to= @.ninka_day_name_to,ninka_date_kyou= @.ninka_date_kyou where syain_No = " + Request.QueryString("syain_No")+ " and date_kyou ='" + Request.QueryString("date_kyou") + "' and time_kyou ='" + Request.QueryString("time_kyou") + "'"

sqlcom.Parameters.AddWithValue("@.ninka_nen_from", TextBox1.Text)
sqlcom.Parameters.AddWithValue("@.ninka_gatsu_from", TextBox2.Text)
sqlcom.Parameters.AddWithValue("@.ninka_hi_from", TextBox3.Text)
sqlcom.Parameters.AddWithValue("@.ninka_day_name_from", TextBox5.Text)
sqlcom.Parameters.AddWithValue("@.ninka_ji_from", textbox_from_hr.Text)
sqlcom.Parameters.AddWithValue("@.ninka_bun_from", textbox_from_min.Text)
sqlcom.Parameters.AddWithValue("@.ninka_nen_to", TextBox6.Text)
sqlcom.Parameters.AddWithValue("@.ninka_gatsu_to", TextBox7.Text)
sqlcom.Parameters.AddWithValue("@.ninka_hi_to", TextBox8.Text)
sqlcom.Parameters.AddWithValue("@.ninka_day_name_to", TextBox9.Text)
sqlcom.Parameters.AddWithValue("@.ninka_ji_to", textbox_to_hr.Text)
sqlcom.Parameters.AddWithValue("@.ninka_bun_to", textbox_to_min.Text)
Label28.Visible = True
textbox_approval_date.Visible = True
textbox_approval_date.Text = Date.Today
statusbox_approve_mesg.Visible = True
sqlcom.Parameters.AddWithValue("@.ninka_date_kyou", textbox_approval_date.Text)

sqlcom.ExecuteNonQuery()
MyConn.Close()
sqlcom.Dispose()
MyConn.Dispose()
MsgBox(" Application Dates was Approved")

TextBox1.Enabled = False
TextBox2.Enabled = False
TextBox3.Enabled = False
TextBox6.Enabled = False
TextBox5.Enabled = False
TextBox7.Enabled = False
TextBox8.Enabled = False
TextBox9.Enabled = False
textbox_from_hr.Enabled = False
textbox_from_min.Enabled = False
textbox_to_hr.Enabled = False
textbox_to_min.Enabled = False
Reason.Enabled = False
compensantory.Enabled = False
Edit1.Visible = False
Button_save.Visible = False
Button_approve.Visible = False
Button_reject.Visible = False
Button_send.Visible = True

&&&& my code... what u think? there is an error in thebold words

and date_kyou and time_kyou are character string only...

its only character string... and its working when i just put requerystring(syain_No) but when i included the date_kyou and time_kyou,.,,, it produces that kind of error...

what do u think is the problem here?

&&&

error

ived been properly arranging the quotes and single quotes but still it has error...it changes the error but has error still...

wat u think about it?

'2006' 付近に不適切な構文があります。 (There is an improper syntax in the vicinity ..'20 06'... )

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: '2006' 付近に不適切な構文があります。

Source Error:

Line 113: sqlcom.Parameters.AddWithValue("@.ninka_date_kyou", textbox_approval_date.Text)Line 114:Line 115: sqlcom.ExecuteNonQuery()Line 116: MyConn.Close()Line 117: sqlcom.Dispose()

|||

Hi,

im assuming that your data type fordate_kyou andtime_kyou are nvarchar(12)

what is the value of the query string?
kindly check if theres any ' in your querystring which will spoil your sql statement.
to minimise this error, use <string>.Replace("'", "''")
Example:

Cstr(Request.QueryString("time_kyou")).Replace("'", "''")

im afraid if this doesnt work you would have to provide the actual value of the query string...

How can I find the number of days in a month?

Hi -- I am new to SQL programming, so this may be an easy one. My query is
picking up a date out of my database table. Then, I need to find the number
of days in the month that the date falls into (for example, the if date in m
y
table is 2/1/2006, then I need to know the number of days in February 2006).
Any ideas about how to do this?
Thank you,
--
LaurieTSELECT
DATEPART(dd, DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, [YourDate]), 0)))
"LaurieT" wrote:

> Hi -- I am new to SQL programming, so this may be an easy one. My query i
s
> picking up a date out of my database table. Then, I need to find the numb
er
> of days in the month that the date falls into (for example, the if date in
my
> table is 2/1/2006, then I need to know the number of days in February 2006
).
> Any ideas about how to do this?
> Thank you,
> --
> LaurieT|||Sorry, being really sloppy today :-(
SELECT
DATEPART(dd, DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, [YourDate]) + 1,
0)))
"Mark Williams" wrote:
> SELECT
> DATEPART(dd, DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, [YourDate]), 0)))
>
> --
>
> "LaurieT" wrote:
>|||http://www.aspfaq.com/show.asp?id=2519
"LaurieT" <LaurieT@.discussions.microsoft.com> wrote in message
news:014D1958-18A4-4DA5-84B1-1204A9EA2E47@.microsoft.com...
> Hi -- I am new to SQL programming, so this may be an easy one. My query
> is
> picking up a date out of my database table. Then, I need to find the
> number
> of days in the month that the date falls into (for example, the if date in
> my
> table is 2/1/2006, then I need to know the number of days in February
> 2006).
> Any ideas about how to do this?
> Thank you,
> --
> LaurieT|||Thank you for this code. I tried it against my SQL Server version of my
database and it works great, but I need to run something similar against my
client's Oracle database -- do you have a suggestion for that?
--
LaurieT
"Mark Williams" wrote:
> Sorry, being really sloppy today :-(
> SELECT
> DATEPART(dd, DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, [YourDate]) + 1,
> 0)))
>
> --
> "Mark Williams" wrote:
>|||select trunc(last_day(sysdate),'DDD') - trunc(sysdate, 'MON') + 1 from
dual|||Alexander Kuznetsov wrote:
> select trunc(last_day(sysdate),'DDD') - trunc(sysdate, 'MON') + 1 from
> dual
Hi There,
Declare @.dat datetime
Set @.dat = '20060503'
Select DatePart(dd,DateAdd(dd,-DatePart(dd,@.dat),DateAdd(mm,1,@.dat)))
May this help you
With Warm regards
Jatinder Singh