How to insert the date

Dear all,
I got a fieldA as datetime (length 8).
When I select in query analyzer, it shows the following value
: 2003-10-27 11:36:27.640
now I get fieldA into a ADODB.recordset in VB with fields
rs_A("fieldA")
And I try to update another table with Field (FieldB) Field B
is also datetime with length 8)
with rs_A ("fieldA") i.e. update tableA set FieldB =
rs_A("fieldA")
The final update statement is query analyzer is :
update tableA set FieldB = 2003-10-27 11:36:27.640
The query analyzer complains incorrect syntax near 11:36 . It
cannot be updated.
How can I insert to FieldB with the complete date and time from
FieldA. Is that something like
update tableA set FieldB = '2003-10-27 11:36:27.640' or
update tableA set FieldB = format(2003-10-27 11:36:27.640)
Please help.
Thanks.hon123456 a écrit :
> Dear all,
> I got a fieldA as datetime (length 8).
> When I select in query analyzer, it shows the following value
> : 2003-10-27 11:36:27.640
> now I get fieldA into a ADODB.recordset in VB with fields
> rs_A("fieldA")
> And I try to update another table with Field (FieldB) Field B
> is also datetime with length 8)
> with rs_A ("fieldA") i.e. update tableA set FieldB =
> rs_A("fieldA")
> The final update statement is query analyzer is :
> update tableA set FieldB = 2003-10-27 11:36:27.640
> The query analyzer complains incorrect syntax near 11:36 . It
> cannot be updated.
> How can I insert to FieldB with the complete date and time from
> FieldA. Is that something like
> update tableA set FieldB = '2003-10-27 11:36:27.640' or
> update tableA set FieldB = format(2003-10-27 11:36:27.640)
1) there si no format about DATETIME SQL type storage it is just
DATETIME (in fact two integers)
2) when casting a string to a DATETIME the only format that is universal
is the ISO short DATETIME format which is :
YYYYMMDD HH:MM:SS.nnn
3) your query : "update tableA set FieldB = 2003-10-27 11:36:27.640"
try to make a substract of 2003 minus 10 minus 27 ... and is loose after
4) if you want to use exotic formats about DATETIME as string, set your
session dateformat parameter as above :
SET DATEFORMAT { ymd | mdy | dmy | ydm | myd | ymd }
5) prefer always use a explicit CAST like :
SET DATEFORMAT myd
SELECT CAST('03199722 00:11:22.333' AS DATETIME)
...
A +

> Please help.
> Thanks.
>
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Thanks SQLpro,
For my query : update tableA set FieldB = 2003-10-27
11:36:27.640
Query Analyzer complains incorrect syntax near 11. What can I do to
make this query work?
Thanks.|||Thanks SQLpro,
Sorry to post again. I do not understand your point 3
suggestion
3) your query : "update tableA set FieldB = 2003-10-27 11:36:27.640"
try to make a substract of 2003 minus 10 minus 27 ... and is loose
after
Is that mean I need to change the query as follows:
update tableA set FieldB = 2003-10-27-11:36:27.640
Thanks.|||On 25 Apr 2006 18:06:51 -0700, "hon123456" <peterhon321@.yahoo.com.hk>
wrote:

>Is that mean I need to change the query as follows:
>update tableA set FieldB = 2003-10-27-11:36:27.640
Put quotes around the date string, and remove the dash between the
date and time:
update tableA set FieldB = '2003-10-27 11:36:27.640'
Roy Harvey
Beacon Falls, CT|||> Put quotes around the date string, and remove the dash between the
> date and time:
... and better yet, put a T between the date and a time part, making int th
e ISO 8601 format which
isn't dependent on any language setting for the login, etc.
(Hi, Roy! I'm happy to see you here :-) .)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:h3lt42hna2q4odv454dpujnrp1hib49od6@.
4ax.com...
> On 25 Apr 2006 18:06:51 -0700, "hon123456" <peterhon321@.yahoo.com.hk>
> wrote:
>
> Put quotes around the date string, and remove the dash between the
> date and time:
> update tableA set FieldB = '2003-10-27 11:36:27.640'
> Roy Harvey
> Beacon Falls, CT

How to insert date into database SQL 2000?

How to insert date into database SQL 2000 that the date read from server ?INSERT INTO tableName ( dateCOlumn) VALUES ('4 Jul 2003')

now if this isn't what you want, how about you post again with some actual detail?|||Opps sorry... i means... first, how to to declare the date using asp.net language... then how to insert it into database|||in VB you can just use Now()

in C# use DateTime.Now;

concatenate that into a SQL string, or assign it to a parameter of a command, then execute it and away you go.|||How can i divide the date and time separately. for example... i want to generate a search coding. its can search the data by day, month or year. I think in my case, the time is not important but I still need the information as knowledge. my problem is, the date that i insert into database keep the full date format. can u solve my problem & recommend any good references. Sorry... i'm still new in asp.net|||well, you can do that in your SQL Code with DatePart() or in .NET take a look under "DateTime object" in the documentation - there's a lot of stuff in there.sql

How to Insert date in sql server database

How to insert date to the sql server database.

I am getting input from the HTML form and store it to database using
ASP.

how to store date field, what datatype needed and what conversion
needed.

Thanx & Regards,
SSGSSG (ssg14j@.gmail.com) writes:
> How to insert date to the sql server database.
> I am getting input from the HTML form and store it to database using
> ASP.
> how to store date field, what datatype needed and what conversion
> needed.

The data type to use datetime or smalldatetime. These always include
the time portion, but set it to midnight for dates only.

Conversion should occur in the client, by using parameterised statements.

Here is an example that I have canned of a parameterised statement in ADO
(it's VB6 and not ASP, but I believe that they are not too different):

Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cnn

cmd.CommandType = adCmdText
cmd.CommandText = " SELECT OrderID, OrderDate, CustomerID, ShipName " & _
" FROM dbo.Orders WHERE 1 = 1 "
If custid <> "" Then
cmd.CommandText = cmd.CommandText & " AND CustomerID LIKE ? "
cmd.Parameters.Append
cmd.CreateParameter("@.custid", adWChar, adParamInput, 5, custid)
End If

If shipname <> "" Then
cmd.CommandText = cmd.CommandText & " AND ShipName LIKE ? "
cmd.Parameters.Append cmd.CreateParameter("@.shipname", _
adVarWChar, adParamInput, 40, shipname)
End If

Set rs = cmd.Execute

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

How to insert date in database table in sql server

I have one form with fields name,date of birth , age.
I would like to get the input from user and store it to sql database
table.
After I stored , while viewing the table records, the dateof birth
field is 01.01.1900, how to store the exact date in database.
what datatype i have to store in database table.
Thanx & Regards,
SSGSSG wrote:
> I have one form with fields name,date of birth , age.
> I would like to get the input from user and store it to sql database
> table.
> After I stored , while viewing the table records, the dateof birth
> field is 01.01.1900, how to store the exact date in database.
> what datatype i have to store in database table.
> Thanx & Regards,
> SSG
Use datetime datatype and you can get plenty of information regarding
it in BOL.
Regards
Amish Shah

How to insert date in database table in sql server

I have one form with fields name,date of birth , age.
I would like to get the input from user and store it to sql database
table.
After I stored , while viewing the table records, the dateof birth
field is 01.01.1900, how to store the exact date in database.
what datatype i have to store in database table.
Thanx & Regards,
SSGSSG wrote:

> I have one form with fields name,date of birth , age.
> I would like to get the input from user and store it to sql database
> table.
> After I stored , while viewing the table records, the dateof birth
> field is 01.01.1900, how to store the exact date in database.
> what datatype i have to store in database table.
> Thanx & Regards,
> SSG
Use datetime datatype and you can get plenty of information regarding
it in BOL.
Regards
Amish Shah

How to insert current date in SQL?

Hi guys,

how do i insert the current date in SQL?

GetDate()

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

Hope this helps.

|||

You can get the value of the current date in thegetdate() function.

How to insert a datetime to sql sever

Hi everyone,

I need help in inserting a date time string to sql sever. For example, how do you insert textbox1.text="2006-08-30 09:00:00" into a datatable column names starttime (type datetime) in sql sever? How do I covert the format of this string before I do the insert?

Thanks.

a123.

You don't have to pass that value. In the colum starttime set the data type to 'starttime' then for the default value enter getdate()

That should fix your problem.

|||

That would be an acceptable format (YYYY-MM-DD HH:mm:ss), of course you lose the sub-second precision though. If you always want to insert the current datetime, then as the previous poster said, change your insert statement to use getdate() rather than take the value from a passed parameter.

How to include the date as part of the log file name...

Hello to all,
I'm using SQL Server agent to perform some schedule jobs.
I have it setup so that it will write to an result output
file (Jobs Steps -> Advanced -> Output file:
The problem is that I would like to name this output file
such as 'result%date%.log' where %date% is the current
date so that I can get a seperate log file each day.
Does anyone know how to do this ? %date% does not work.
Thanks in advance.
KinKin:
The way I have it up is that I do archiving of these output log files every
day as a separate job. In the archiving process I copy the files and add
date to the filename as follows:
declare @.cmd sysname
declare @.var sysname
set @.var = convert(varchar(11),getdate(),112)
--add date to file and archive
set @.cmd = 'copy e:\Logs\DBMaintenance\*.* e:\Logs\Archive_Logs\*' + @.var +
'.txt'
exec master..xp_cmdshell @.cmd
"Kin" <ninka_2000@.yahoo.com> wrote in message
news:017501c34fa8$212552a0$a301280a@.phx.gbl...
> Hello to all,
> I'm using SQL Server agent to perform some schedule jobs.
> I have it setup so that it will write to an result output
> file (Jobs Steps -> Advanced -> Output file:
> The problem is that I would like to name this output file
> such as 'result%date%.log' where %date% is the current
> date so that I can get a seperate log file each day.
> Does anyone know how to do this ? %date% does not work.
> Thanks in advance.
> Kin|||I'm not sure that there is a direct way to do that in SQL Agent. You could
always build you're own backup log command and execute that directly. Also,
you could simply let the job name it what it wants, and then add a step that
uses xp_cmdshell to rename the file.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Kin" <ninka_2000@.yahoo.com> wrote in message
news:017501c34fa8$212552a0$a301280a@.phx.gbl...
> Hello to all,
> I'm using SQL Server agent to perform some schedule jobs.
> I have it setup so that it will write to an result output
> file (Jobs Steps -> Advanced -> Output file:
> The problem is that I would like to name this output file
> such as 'result%date%.log' where %date% is the current
> date so that I can get a seperate log file each day.
> Does anyone know how to do this ? %date% does not work.
> Thanks in advance.
> Kin|||>--Original Message--
>Hello to all,
>I'm using SQL Server agent to perform some schedule
jobs.
>I have it setup so that it will write to an result output
>file (Jobs Steps -> Advanced -> Output file:
>The problem is that I would like to name this output file
>such as 'result%date%.log' where %date% is the current
>date so that I can get a seperate log file each day.
>Does anyone know how to do this ? %date% does not work.
>Thanks in advance.
>Kin
>.
>

How to improve query

Following is a query based on Northwind. I need to output the highest
order value for each date.
The following works. However I want to see if there is any way to
remove the nested query and do some joins.
select a.orderid, a.orderdate, sum(b.quantity * b.unitprice) as total
from orders a, [order details] b
where a.orderid = b.orderid
group by a.orderid, a.orderdate
having sum(b.quantity * b.unitprice) = (select max(total1)
from (select sum(quantity*unitprice) as total1, orders.orderdate as
date, orders.orderid
from [order details], orders
where [order details].orderid = orders.orderid
group by orders.orderdate, orders.orderid
) as C
where c.date = a.orderdate)
order by a.orderdate asc
Thanks,Here is another way (but I'm not sure if it's better, though; in fact,
the execution plan indicates that it's worse):
select x.orderid, x.orderdate, x.total
from (
select o1.orderid, o1.orderdate,
sum(d1.quantity * d1.unitprice) as total
from orders o1
inner join [order details] d1 on o1.orderid = d1.orderid
group by o1.orderid, o1.orderdate
) x inner join (
select orderdate, max(total) as max_total
from (
select o2.orderid, o2.orderdate,
sum(d2.quantity * d2.unitprice) as total
from orders o2
inner join [order details] d2 on o2.orderid = d2.orderid
group by o2.orderid, o2.orderdate
) y
group by orderdate
) z on x.orderdate=z.orderdate and x.total=z.max_total
order by x.orderdate
We can use a view (or a CTE in SQL Server 2005), instead of the x and y
derived tables, but this will not improve the performance (just the
readability).
Razvan

how to implement user function/query

Hi,

In my database I have a table which is some kind of history log. The report is designed to group these entries by date. Showing the history data for the entries works fine, but for any history entry I need to show its previous entry, too (that means the table row with maximum date being smaller than the current selected). But what function/query do I have to implement?

Help would be highly appreciated.

Thank you,
gbruse
Select statement with between, and|||But how can I use the result of a select statement in a function? What I wanted to do is getting all dates and getting the latest which is below the current selected (of the group).

gbr|||send me ur code and table structure|||+------+-------+--+--+---+-------+
| Field | Type | Null | Key | Default | Extra |
+------+-------+--+--+---+-------+
| id | int(10) unsigned | | PRI | NULL | auto_increment |
| id_cust | int(10) unsigned | | | 0 | |
| add_data | varchar(20) | YES | | NULL | |
+------+-------+--+--+---+-------+

Imagine you have a table with the dates a user bought something. What I want to achive is showing all items groupes by dates and the date before the selected date.

The report shall look like:

date customer goods
old 06/02/05 Mr. Brown ...
now 06/03/05 Mr. Brown ...
-------------------
old 07/02/05 Mrs. Green ...
new 07/24/05 Mrs. Green ...

Thanks for your replay,

gbr.|||It would help if somebody could tell how to write a query (select xyz from tab where ...) and how to store the return value into a variable. Giving an example to familiarize with the crystal syntax would be enough for the beginning.

gbr|||Is there anyone who can tell how to store an entire column of a table in a variable?

When I use this function

local datetimevar array dates := {tab.date};
count(dates);

Only 1 is displayed though there are more than 1 columns in this table...

How to ignore timepart when using LookUp with date.

Hi

I want to lookup the datekeys from my datedimension. However it does not work because matching for same dates does not work because of different timeparts. The dates in my lookup-table have a 00:00:00 timepart, but the dates in my input table have a non-zero timepart. How can I ignore the timeparts or make the timeparts zero?

Regards,
HenkI found a workaround:

add a derived column component before and cast to DT_DBDATE first and than back to DT_DBTIMESTAMP to get rid of the timepart: (DT_DBTIMESTAMP)(DT_DBDATE)DateColumn.

I am still interested in a solution that doesn't require an extra component.

Henk|||Can you not use T-SQL to change the data at source?

-Jamie|||Yes that's true, but the sports of SSIS is in doing it without coding T-SQL

How to hide/show parameter dynamic?

There is a date range parameter in my rdl, the list value is "This Week","This Month", "This Year" and "Custom", when user choose "Custom", then display two parameter "Date From" and "Date To" to select custom date range.

My question, how to show "Date From" and "Date To" when user choose "Custom", and will hide these two parameter when user choose other item.

Thanks

You don't say what kind of element Date To and Date From are, but if they are text boxes with lables, you can change their Visible property from "False" to "True" or vice versa, depending on what the user has selected. I suggest that the default poroperty should be False., then make them Visisble if the user chooses "Custom"|||

Thanks for you reply

But the parameter's visible property cannot support formula, and I cannot get the change event of parameter "Date Range", such as selectedIndexChange

I use SRS2005, the "Date Range" is a dropdownlist, "Date From" and "Date To" may be the text box

How to hide/show parameter dynamic?

There is a date range parameter in my rdl, the list value is "This
Week","This Month", "This Year" and "Custom", when user choose "Custom", then
display two parameter "Date From" and "Date To" to select custom date range.
My question, how to show "Date From" and "Date To" when user choose
"Custom", and will hide these two parameters when user choose other item.
I use SRS2005
If cannot design the above function, I want to know, how to set the layout
of 3 parameters as:
Date Range ______
Date From ______ Date To ______
The SRS will display as:
Date Range ______ Date From ______
Date To ______
ThanksIf the parameters values are coming from query byway of datasets then it can
be cascaded so whn you select "custom" then it displayes the from and to
otherwise I suppose it is not possible. when you create 3 parameters then it
displays all 3.
Amarnath
"icyer" wrote:
> There is a date range parameter in my rdl, the list value is "This
> Week","This Month", "This Year" and "Custom", when user choose "Custom", then
> display two parameter "Date From" and "Date To" to select custom date range.
> My question, how to show "Date From" and "Date To" when user choose
> "Custom", and will hide these two parameters when user choose other item.
> I use SRS2005
> If cannot design the above function, I want to know, how to set the layout
> of 3 parameters as:
> Date Range ______
> Date From ______ Date To ______
> The SRS will display as:
> Date Range ______ Date From ______
> Date To ______
> Thanks
>|||Hi
I'd be curious as to your coding behind this. I need to do similar in
giving a list that has predefined dates to be passed except when the user
chooses their own dates.
Thanks
chelle
"icyer" wrote:
> There is a date range parameter in my rdl, the list value is "This
> Week","This Month", "This Year" and "Custom", when user choose "Custom", then
> display two parameter "Date From" and "Date To" to select custom date range.
> My question, how to show "Date From" and "Date To" when user choose
> "Custom", and will hide these two parameters when user choose other item.
> I use SRS2005
> If cannot design the above function, I want to know, how to set the layout
> of 3 parameters as:
> Date Range ______
> Date From ______ Date To ______
> The SRS will display as:
> Date Range ______ Date From ______
> Date To ______
> Thanks
>