Adding a sysdate to a tablename

Hi basically Im creating a package and I need to back up the table and put the date in the name of the table whenever the package is run, however I get an error when I try and do it, I think it should be possible but im not sure:

SELECT *
INTO tablename + CONVERT(char(10), GETDATE(), 112)
FROM tablename

So the tablename should looksomething like this

tablename20031210

The error message is: incorrect syntax near +

Can anybody help.

ThanksUse dynamic Sql within pl/sql:

l_statement varchar2(100);
begin
l_statement:=' create table name_'||to_char(sysdate,'DDMMRRRR');
l_statement:=l_statement||define colums here

execute immediate l_statement;
end;

eventually you must append an ; at the end of the varchar2 because it is an DDL-Statement

Adding a sysdate to a table

Hi basically Im creating a package and I need to back up the table and put the date in the name of the table whenever the package is run, however I get an error when I try and do it, I think it should be possible but im not sure:

SELECT *
INTO tablename + CONVERT(char(10), GETDATE(), 112)
FROM tablename

So the tablename should looksomething like this

tablename20031210

The error message is: incorrect syntax near +

Can anybody help.

ThanksMaybe...

Declare @.Query nVarchar(1000)

SET @.Query=N'Select * From ' + 'tablename' + CONVERT(char(10), GETDATE(), 112)

EXECUTE sp_executesql @.Query, N'@.level tinyint', @.level = 35|||Thanks a lot this has been driving me crazy. I had to modify the code slightly but it works fine.

DECLARE @.Query nVarchar(1000)
SET @.Query = N'Select * INTO ' + 'tablename' + CONVERT(char(10), GETDATE(), 112) + 'FROM tablename' EXECUTE sp_executesql @.Query, N'@.level tinyint',
@.level = 35

Adding a script in Reporting Services

Hi.
I need to display a date with his serial week number of the year (from 1-52
weeks).
I wrote a class method that will receive this date and will return the
corresponding week number, and made a dll file out of it.
I added this dll as a reference to my report using Report->Report
Properties->Add Reference.
Now to the tricky part, how do I embed this code in my report?
I found some documented material that I need to uncomment the
<customAssembies> in the rsconfig file. I looked into my config file but no
such <customAssembies> exists.
Does anyone have an example of how to use external dll and to successfully
embed them in the report?
I will highly appreciate any word of wisdom...
Thanks,
GuyGuy,
I didn't figure out your dll question but I believe this will solve
your date problem.
=DatePart(DateInterval.WeekOfYear,Today)
or
=DatePart("ww",Today)
Hope this helps.
Here's a link to the Visual Basic Run-time Library Members
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vaoriVBRuntimeLibraryKeywords.asp
Steve|||Steve, thanks a lot.
You have been very helpful.
I also realized how to create GetWeekOfYear( ) in VB.Net and to embed this
function in my report as Code.GetWeekOfYear( ).
I would to rephrase my request for assistance:
1. I created a class in C# and the class method as GetWeekOfYear( ) and
create a dll file of this class.
2. I added this reference to an existing report using the Report->Add
reference command from the Menu toolbar.
3. According to MSDN documentation I need to uncomment the
<CustomAssemblies> in the RSReportingServer.config
However, I didn't find any <CustomAssemblies> in the
RSReportingServer.config, therefore I can't refer to my dll file which
contains the GetWeekOfYear( ) method.
I will appreciate any help in adding and using dll in the Reporting Services
(which is not written in VB.Net which is the natural language in this
environment).
Thanks,
Guy
"sjeffrey@.gmail.com" wrote:
> Guy,
> I didn't figure out your dll question but I believe this will solve
> your date problem.
> =DatePart(DateInterval.WeekOfYear,Today)
> or
> =DatePart("ww",Today)
> Hope this helps.
> Here's a link to the Visual Basic Run-time Library Members
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vaoriVBRuntimeLibraryKeywords.asp
> Steve
>|||Why not simply have a reference date table that contains a record for every
date and the week numbers in the record? Then you solve the problem with a
join at the data source. This reference date table has plenty of benefits
as mentioned in other recent posts.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"GuyR" <GuyR@.discussions.microsoft.com> wrote in message
news:4B1CFD27-5D6C-44BA-A07B-D2F186EC4876@.microsoft.com...
> Hi.
> I need to display a date with his serial week number of the year (from
> 1-52
> weeks).
> I wrote a class method that will receive this date and will return the
> corresponding week number, and made a dll file out of it.
> I added this dll as a reference to my report using Report->Report
> Properties->Add Reference.
> Now to the tricky part, how do I embed this code in my report?
> I found some documented material that I need to uncomment the
> <customAssembies> in the rsconfig file. I looked into my config file but
> no
> such <customAssembies> exists.
> Does anyone have an example of how to use external dll and to successfully
> embed them in the report?
> I will highly appreciate any word of wisdom...
> Thanks,
> Guy
>sql

Adding a default value to a table

I'm new to SQL server and am in the process of converting an Access Db to SQL.

What I'd like to happen is for the current date and an Autonumber generated as soon as the user enters a value in another field. This was fairly straightforward in Access. When I try to set the default in design table (inSQL), the date() is not available. After a little research I came up with CREATE DEFAULT and Bind column commands, Global variables, etc.

I have a couple of questions for anyone that might know:

Is there an easier way than this to create default values in a table? AND,

Would I also have to bind columns for Table lookups? For example, if I wanted a price to be returned after the product ID was entered (Automatic in Access if relationship/form setup properly), would I have to bind the column? Or would it happen automatically?you can use getdate() or current_timestamp instead of date()

for "lookup" feature of access you can create calculated field.

Adding a day to a date.

Pls help if you can. I have two dates, date1 and date2.
Basically I want to set date2 = date1 + 1 day for a range of dates
that I can select out.

Is there any way to do this?"Chachu" <cpatel1@.gmail.com> wrote in message
news:d7c4c47b.0410050757.6d4bc3a1@.posting.google.c om...
> Pls help if you can. I have two dates, date1 and date2.
> Basically I want to set date2 = date1 + 1 day for a range of dates
> that I can select out.
> Is there any way to do this?

See DATEADD in Books Online:

update dbo.MyTable
set date2 = dateadd(dd, 1, date1)
where ...

Simon

Adding a date to a report

I'd like to add a date in the heading of the report. Since the current date is not part of the stored procedure that I'm using how can I get the date? I was hoping for a built-in function of some kind.

you should be able to just add a textbox and enter the expression

= System.DateTime.Now()

|||

Are you referring to referencing a date/time other than the current date/time? If so, I would like to know how to do this as well. I have a DTS (SSIS) job that i want to reference the time it ran in my report. I could do the same thing with a stored proc if I had to.

Thanks everyone.

|||No, all I needed was the date/time the report is produced and I already received that answer.sql

add working days to a date

I want to be able to pass a date to a function and add 4 days to it. The
quirk is that it needs to be 4 working days ie. Mon - Fri excl holidays.
so
I have a table of Holidays with 2 records in 2/1/2006 (2nd Jan) and 3/1/2005
(3rd Jan)
The 31/12/05 and 1/1/06 are a Sat and Sun
I have a date 30/12/2005 to which I want to add 4 working days (this to be
variable) therefore the date returned would be 9/1/06 (9th Jan)
In vb I made use of the Wday function and DLookup
How would I do the same thing in SQL
Thanks
CREATE TABLE [dbo].[tblHols] (
[Holiday] [datetime] NOT NULL
) ON [PRIMARY]
GO>I want to be able to pass a date to a function and add 4 days to it. The
>quirk is that it needs to be 4 working days ie. Mon - Fri excl holidays.
Your best bet is a calendar table. Please see http://www.aspfaq.com/2519
for a thorough treatment (I think one of the examples even shows you how to
estimate delivery date, which sounds pretty much like what you're asking
for).|||I have created the calendar table as suggested but the select query takes 43
secs to run. How could I speed this up? A similar function in VB takes far
less time.
Here are the scripts
CREATE TABLE dbo.Calendar
(
dt SMALLDATETIME NOT NULL
PRIMARY KEY CLUSTERED,
isWday BIT,
isHoliday BIT,
Y SMALLINT,
FY SMALLINT,
Q TINYINT,
M TINYINT,
D TINYINT,
DW TINYINT,
monthname VARCHAR(9),
dayname VARCHAR(9),
W TINYINT
)
GO
SET NOCOUNT ON
DECLARE @.dt SMALLDATETIME
SET @.dt = '20060101'
WHILE @.dt < '20300101'
BEGIN
INSERT dbo.Calendar(dt) SELECT @.dt
SET @.dt = @.dt + 1
END
UPDATE dbo.Calendar SET
isWday = CASE
WHEN DATEPART(DW, dt) IN (1,7)
THEN 0
ELSE 1 END,
isHoliday = 0
UPDATE Calendar
SET
isHoliday = 1,
WHERE M = 1
AND D = 1
UPDATE Calendar
SET
isHoliday = 1,
WHERE M = 1
AND D = 2
UPDATE Calendar
SET
isHoliday = 1,
WHERE M = 1
AND D = 3
UPDATE Calendar
SET
isHoliday = 1,
WHERE M = 1
AND D = 4
Declare @.dte datetime
SET @.dte = '20060101'
SELECT c.dt
FROM dbo.Calendar c
WHERE
c.isWday = 1
AND c.isHoliday =0
AND 9 = (
SELECT COUNT(*)
FROM dbo.Calendar c2
WHERE c2.dt >= @.dte
AND c2.dt <= c.dt
AND c2.isWday=1
AND c2.isHoliday=0
)
Thanks
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23nltssPAGHA.2984@.TK2MSFTNGP09.phx.gbl...
> Your best bet is a calendar table. Please see http://www.aspfaq.com/2519
> for a thorough treatment (I think one of the examples even shows you how
> to estimate delivery date, which sounds pretty much like what you're
> asking for).
>|||On Thu, 15 Dec 2005 09:52:05 -0000, Newbie wrote:

>I have created the calendar table as suggested but the select query takes 4
3
>secs to run. How could I speed this up? A similar function in VB takes fa
r
>less time.
Hi Newbie,
Just to clarify: you ARE aware that the Calendar table should be a
permanent one, aren't you? Create and populate it once, then just use
it. Don't drop after use and re-create before the next use.

>Declare @.dte datetime
>SET @.dte = '20060101'
>SELECT c.dt
> FROM dbo.Calendar c
> WHERE
> c.isWday = 1
> AND c.isHoliday =0
> AND 9 = (
> SELECT COUNT(*)
> FROM dbo.Calendar c2
> WHERE c2.dt >= @.dte
> AND c2.dt <= c.dt
> AND c2.isWday=1
> AND c2.isHoliday=0
> )
To speed this up, try this modification:
SELECT c.dt
FROM dbo.Calendar c
WHERE
c.isWday = 1
AND c.isHoliday =0
AND c.dt > @.dte
AND 9 = (
SELECT COUNT(*)
FROM dbo.Calendar c2
WHERE c2.dt >= @.dte
AND c2.dt <= c.dt
AND c2.isWday=1
AND c2.isHoliday=0
)
For a real speed gain, find a reasonable ratio for non-business days vs
total days and round up to be on the safe side. To be on the safe side,
I'll use a ratio of 1 to 2:
SELECT c.dt
FROM dbo.Calendar c
WHERE
c.isWday = 1
AND c.isHoliday =0
AND c.dt > @.dte
AND c.dt <= DATEADD(day, 9 * 2, @.dte)
AND 9 = (
SELECT COUNT(*)
FROM dbo.Calendar c2
WHERE c2.dt >= @.dte
AND c2.dt <= c.dt
AND c2.isWday=1
AND c2.isHoliday=0
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Having just posted a different project regarding dates, I thought I would gi
ve this a shoot. It turned into more work then expected, but should be nice
for a toolbox. This solution appears to run in the millisecond range and I
could probably get it faster. Currently, for holidays, I am calculating th
em each call. I could cache using a hashtable or something, but the speed s
till seems to be ok in any event. Attached is a class that contains many bus
iness date functions such as:
public static DateTime AddQuarters(DateTime date, int quarters);
public static DateTime AddWorkDays(DateTime startDate, int workDays, Holiday
[] holidays);
public static DateTime AddWorkDays(DateTime startDate, int workDays, DateTim
e[] holidays);
public static DateTime AddWorkDays(DateTime startDate, int workDays, bool ho
lidaysAreWorkDays);
public static string DaysHoursMinutesSecondsMilliseconds(Date
Time start, Dat
eTime end);
public static DateTime[] GetDayOfWInMonth(DateTime date, DayOfW[] days
OfTheW);
public static DateTime[] GetDayOfWInMonth(DateTime date, DayOfW dayOfW
);
public static DateTime[] GetDayOfWInRange(DateTime startDate, DateTime en
dDate, DayOfW dayOfW);
public static DateTime[] GetDayOfWInRange(DateTime startDate, DateTime en
dDate, DayOfW[] daysOfW);
public static DateTime GetEndOf(PeriodType periodType, DateTime date);
public static DateTime GetEndOfDay(DateTime date);
public static DateTime GetEndOfMinute(DateTime date);
public static DateTime GetEndOfMonth(DateTime date);
public static DateTime GetEndOfQuarter(DateTime date);
public static DateTime GetEndOfQuarter(int year, int quarter);
public static DateTime GetEndOfW(DateTime date);
public static DateTime GetEndOfYear(DateTime date);
public static DateTime GetEndOfYear(int Year);
public static Holiday[] GetHolidays(int year);
public static Holiday[] GetHolidays(DateTime startDate, DateTime endDate);
private static DateTime GetNextQuarter(DateTime date);
private static DateTime GetPriorQuarter(DateTime date);
public static int GetQuarter(DateTime date);
public static DateTime GetStartOf(PeriodType periodType, DateTime date);
public static DateTime GetStartOfDay(DateTime date);
public static DateTime GetStartOfMinute(DateTime date);
public static DateTime GetStartOfMonth(DateTime date);
public static DateTime GetStartOfQuarter(DateTime date);
public static DateTime GetStartOfQuarter(int year, int quarter);
public static DateTime GetStartOfW(DateTime date);
public static DateTime GetStartOfYear(DateTime date);
public static DateTime GetStartOfYear(int Year);
public static DateTime[] GetWorkDaysInRange(DateTime startDate, DateTime end
Date, Holiday[] holidays);
public static DateTime[] GetWorkDaysInRange(DateTime startDate, DateTime end
Date, DateTime[] holidays);
public static DateTime[] GetWorkDaysInRange(DateTime startDate, DateTime end
Date, bool holidaysAreWorkDays);
public static string HoursAndMinutes(DateTime start, DateTime end);
public static bool IsBetweenDay(DateTime date, DateTime day);
public static bool IsBetweenDays(DateTime date, DateTime startDate, DateTime
endDate);
public static bool IsBetweenMonth(DateTime date, DateTime month);
public static bool IsBetweenQuarter(DateTime date, int quarter);
public static bool IsBetweenQuarter(DateTime date, int year, int quarter);
public static bool IsHoliday(DateTime date);
public static bool IsHoliday(DateTime date, Holiday[] holidays);
public static bool IsHoliday(DateTime date, DateTime[] holidays);
public static bool IsWorkDay(DateTime date);
// The following is a CLR UDF that calls this class for AddWorkDays. This e
xample udf calls DateFunctions.AddWorkDays() and skips any of the 10 Federal
holidays. You could also add your own holidays (i.e. non-work days) add ca
ll a different overload. Other UDFs that call the different static methods
on the DateFunctions class could be written as well.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using SqlUtils;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static DateTime AddWorkDays(DateTime date, int workDays)
{
DateTime start = DateTime.Now;
DateTime day = DateFunctions.AddWorkDays(date, workDays, false);
DateTime end = DateTime.Now;
TimeSpan ts = end - start;
Console.WriteLine(ts.ToString());
return day;
}
};
Please let me know if you have any questions. I still need more testing on
everything, but the AddWorkDays seems to be working pretty well. Let me kno
w if you questions. Cheers!
--
William Stacey [MVP]|||Brilliant! It now takes less than 1 second when using the second mod
Thanks
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:o0v3q1t8ckqoo7em98ull7k3s0vnrglksg@.
4ax.com...
> On Thu, 15 Dec 2005 09:52:05 -0000, Newbie wrote:
>
> Hi Newbie,
> Just to clarify: you ARE aware that the Calendar table should be a
> permanent one, aren't you? Create and populate it once, then just use
> it. Don't drop after use and re-create before the next use.
>
> To speed this up, try this modification:
> SELECT c.dt
> FROM dbo.Calendar c
> WHERE
> c.isWday = 1
> AND c.isHoliday =0
> AND c.dt > @.dte
> AND 9 = (
> SELECT COUNT(*)
> FROM dbo.Calendar c2
> WHERE c2.dt >= @.dte
> AND c2.dt <= c.dt
> AND c2.isWday=1
> AND c2.isHoliday=0
> )
>
> For a real speed gain, find a reasonable ratio for non-business days vs
> total days and round up to be on the safe side. To be on the safe side,
> I'll use a ratio of 1 to 2:
> SELECT c.dt
> FROM dbo.Calendar c
> WHERE
> c.isWday = 1
> AND c.isHoliday =0
> AND c.dt > @.dte
> AND c.dt <= DATEADD(day, 9 * 2, @.dte)
> AND 9 = (
> SELECT COUNT(*)
> FROM dbo.Calendar c2
> WHERE c2.dt >= @.dte
> AND c2.dt <= c.dt
> AND c2.isWday=1
> AND c2.isHoliday=0
> )
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Here is another fun little thing you can do with the lib. Print a calendar o
f any month(s). This basically works by enumerating the Calendar ws for a
ny month using GetMonthCalendarWs as shown in code below:
Output
---
January 2006
SU MO TU WE TH FR SA
01 02 03 04 05 06 07
08 09 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31
February 2006
SU MO TU WE TH FR SA
01 02 03 04
05 06 07 08 09 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28
March 2006
SU MO TU WE TH FR SA
01 02 03 04
05 06 07 08 09 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31
...
// Put in windows or console app. Reference the sqlutils.dll and run.
private void button2_Click(object sender, EventArgs e)
{
Console.WriteLine();
for (int i = 1; i <= 12; i++)
{
PrintMonthCalendar(new DateTime(2006, i, 1));
Console.WriteLine();
}
}
private void PrintMonthCalendar(DateTime date)
{
Console.WriteLine(date.ToString("MMMM yyyy"));
Console.WriteLine("SU MO TU WE TH FR SA");
DateRange[] cal = DateRange.GetMonthCalendarWs(date, false);
for (int i = 0; i < cal.Length; i++)
{
DateRange day = cal[i];
if (day == null)
Console.Write(" " + " ");
else
Console.Write(day.StartDate.Day.ToString().PadLeft(2, '0') + " ");
if ((i != 0) && ((i + 1) % 7 == 0))
Console.WriteLine();
}
}
--
William Stacey [MVP]

Add two digits to the front?

Hi all,
I have a number like so 778625 and want to make
this a more meaningful date (UK) so how difficult is it to
add two digits in this case 19 to the front of this number?
Many thanks
SamThis is a date? I'm not sure I understand how 1977 is any more meaningful
than 77, when the rest of the number is 8625. But okay, I suppose you could
do this:
DECLARE @.i INT
SET @.i = 778625
SELECT '19'+CAST(@.i AS VARCHAR)
And if you have to do it conditionally, say if the first two digits are less
than 30 you assume it's 20xx, then
DECLARE @.i INT, @.ivc VARCHAR(12)
SELECT @.i = 778625, @.ivc = CAST(@.i AS VARCHAR(32))
SELECT CASE WHEN CONVERT(TINYINT, LEFT(@.ivc, 2))>=30 THEN '19' ELSE '20' END
+ @.ivc
"Sam" <sgpgpjr@.yahoo.ie> wrote in message
news:082a01c3462b$f1924960$a001280a@.phx.gbl...
> Hi all,
> I have a number like so 778625 and want to make
> this a more meaningful date (UK) so how difficult is it to
> add two digits in this case 19 to the front of this number?
> Many thanks
> Sam
>

add time to datetime value and split into date and time

Hi

i have the following situation. in my database i have a datetime field (dd/mm/yy hh:mms) and i also have a field timezone.

the timezone field has values in minutes that i should add to my datetime field so i have the actual time.

afterwards i split the datetime into date and time.

the last part i can accomplish (CONVERT (varchar, datetime, 103) as DATEVALUE and CONVERT (varchar, DATETIME, 108) as TIMEVALUE).

could anybody tell me how i can add the timezone value (in minutes) to my datetime value ?

i do all the calculations in my datasource (sql).

Thanks

V.

at the end i found it myself

this is how i solved it.

to add the timezone value (in minutes) to my datetime i used following :

DATEADD(minute, TimeZone, DATETIMEVALUE) AS ACTUALDATETIME

this expression will add the timezone value (example 60) to my current date time value (12/06/2007 08:00:00) and store it in

actualdatetime value (12/06/2007 09:00:00).

from then on it's easy to extract date and time from the result.

CONVERT(varchar, (DATEADD(m,TimeZone,DATETIME)), 103) AS DATEVAL for the DATE

CONVERT(varchar, (DATEADD(m,TimeZone,DATETIME)), 108) AS TIMEVAL for the time.

i thank myself for my research .. lol

Greetings to all

Add the end date to record

I have records with a start date. I would like to add a column that is the
end date, or the next record's start date.
So if I had
Store, startdate, retail
1, 10/15/2005, 2.50
1, 10/30/2005, 3.00
1, 11/19/2006, 2.00
The new records would be:
Store, startdate, retail, end date
1, 10/15/2005, 2.50, 10/30/2005
1, 10/30/2005, 3.00, 11/19/2005
1, 11/19/2006, 2.00,
Thanks for any suggestions.
Alter Table MyTable
Add EndDate Datetime
Go
I haven't a clue what your table is called let anyone any Primary Keys
etc but the update statement would look something similar to :
Update MyTable
Set EndDate = '2005-10/-0'
Where Store = 1
And StartDate = '2005-10-15'
HTH
Barry|||I understand your query, but my table have 2 million rows with many differen
t
stores and start dates.
So I need to add the startdate value from the next (in order of startdate)
record that matches on store and add that to my end date column.
Thank you for your assistance.
"Barry" wrote:

>
> Alter Table MyTable
> Add EndDate Datetime
> Go
> I haven't a clue what your table is called let anyone any Primary Keys
> etc but the update statement would look something similar to :
> Update MyTable
> Set EndDate = '2005-10/-0'
> Where Store = 1
> And StartDate = '2005-10-15'
> HTH
> Barry
>|||please try this:
update tbl1
set enddate=(select min(startdate) from tbl1 t where
t.startdate>tbl1.startdate and t.store=tbl1.store)
dean
"AshleyT" <AshleyT@.discussions.microsoft.com> wrote in message
news:666F7AD4-1868-476F-8D37-A1ECF3C8DF76@.microsoft.com...
>I understand your query, but my table have 2 million rows with many
>different
> stores and start dates.
> So I need to add the startdate value from the next (in order of startdate)
> record that matches on store and add that to my end date column.
> Thank you for your assistance.
> "Barry" wrote:
>|||Without any DDL or Sample Data its very difficult to try to help you.
Please see http://www.aspfaq.com/etiquette.asp?id=5006 for more info
and help
Thanks
Barry|||this answered it perfectly. thanks much!
"Dean" wrote:

> please try this:
> update tbl1
> set enddate=(select min(startdate) from tbl1 t where
> t.startdate>tbl1.startdate and t.store=tbl1.store)
> dean
> "AshleyT" <AshleyT@.discussions.microsoft.com> wrote in message
> news:666F7AD4-1868-476F-8D37-A1ECF3C8DF76@.microsoft.com...
>
>

Add New Record into table

Hi , All
I default value in textbox for show now date and I used to method

txtDate.Text = Now.ToShortDateString

It's have problem when I want to add new record in table

I get error message "The statement has been terminated. String or binary data would be truncated. "

and I set type in database is char length 10 ,I can't set type is datetime because in SQL Server fix type datetime

It's have length 8 but 8 length it's not enough for default value
Please help me
Thanks
Nisarat

First use DateTime.Now.ToShortDateString
Secondly use 'yyyy-MM-dd' eg. '2005-06-01'(notice the single quote ' ) format while inserting data.
|||

Hi, dinuj

I set type of field date is datetime and set default value "dd/mm/yyyy" but I get error message "Error validating the default for column "datekey""
Thanks
Nisarat

|||Do mean to say that you are trying to set a default date for the coloumn datekey
|||

Hi,Dinuj

yes ,I trying set type of field DateKey is DateTime type but ,in SQLServer it's 8 char only and I will trying to defaulf value in this field but It's can not
Thanks
Nisarat

|||

Nisarat wrote:

Hi,Dinuj

yes ,I trying set type of field DateKey isDateTime type but ,in SQLServer it's 8 char only and I willtrying to defaulf value in this field but It's can not
Thanks
Nisarat

Is your DateKey column is of DateTime type? DateTime is 8 bytes widenot 8 chars. Are you using Enterprise Manager ? If yes then clickon Default value and set the default date to whatever you want in thisformat 'yyyyMMdd' . For example for today '20050606'

Add n numbers of days to a date

Hi,

I have a textbox with date selected from calendar... Now i put in another textbox to enters number of day(30 or 120 or other), this number may vary...

so my qns is how to add the n numbers of days entered to the date selected? i stored date as smalldatetime

Help is appreciated

There is a function DATEADD you can use:

DATEADD(dd, yourFirstDate, 30)-- add 30 days to your first date.

you can use this in your insert or update statement. Let me know if you need more help.

|||

U r having the function Dateadd. U use the function

DATEADD(datepart,number,date)

DatepartAbbreviationsYearyy, yyyyquarterqq, qMonthmm, mdayofyeardy, yDaydd, dWeekwk, wwHourhhminutemi, nsecondss, smillisecondms

Thanks

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||

Hi all,

Thank for replying.. Oh y under this section... Anyway i dun quite understand how to implement it in my codes...

Cos my Textbox for user to enter number of days to due date may vary... Like as i say in abv example

Step1: User select the firstdate from a calendar and display in TextBox1.Text (12/11/2007)

Step2: User enter the number of days to due date in TextBox2.Text (example, 30) <-- can be any number...no specific

Step3: On click a button it display the 12/12/2007 in a label maybe...

Step4: this label date is save to sql for future use...

So i need know how to implement it... as most example i googled has a specific days declare... As for step4 is easy so i only need to know step2 and 3

Any details example of how to do it?

Ur help is appreciated

Edit: I have try this codes... it works but how can i not specific the number of days... i wan the user to specific any number of days they want in TextBox2

Dim nowAsNew DateTime

now = tb_dop.Text

Dim lAsNew DateTime

l = now.AddDays(30)

TextBox1.Text = l

|||

OK

Thanks for the helps... I have figure it out as followed

Dim nowAsNew DateTime

now = tb_dop.Text

Dim iAsInteger

i = TextBox1.Text

Dim lAsNew DateTime

l = now.AddDays(i)

TextBox1.Text = l

add leading zero to date column?

Hello All,

None of the solutions I have found in the archives seem to solve my
problem. I have a date column in my tables (stored as a char(10))
which I would like to append a leading zero to for those dates that
start with 9 or lower.

Any ideas?

Thanks,

MikeMyk wrote:
> Hello All,
> None of the solutions I have found in the archives seem to solve my
> problem. I have a date column in my tables (stored as a char(10))

No, you have a char() column in your table that you store a string in
that is supposed to represent a date. It may or may not.

> which I would like to append a leading zero to for those dates that
> start with 9 or lower.

I'd recommend you actually make the column a date data type since that's
what it is for, but you can append like this:

CASE WHEN LEFT(YourColumn, 1) BETWEEN '1' AND '9' THEN '0' + YourColumn END

Now, there are tons of things wrong with what I just wrote in that it
assumes the first column will always be 0-9, which, given your specs
above, isn't garunteed. It assumes that there are no spaces in the first
character position. It assumes that if there is a 1 through 9 that the
length of that string plus the new '0' is still within 10 chars. All in
all, it is a crappy solution.

Make your data column type match your data and this problem goes away.

Zach

> Any ideas?
> Thanks,
> Mike|||Database Modeling Sin # 58 - Confusing Data Presentation with Data
Representation.

If you can't convert the column to its proper datatype (highly , greatly,
strongly, and "i mean it dude!" recommended),
add the following to a trigger on the table to insure that the format of the
string is consistent.

update mytable
set DateLikeCharColumn = IsNull( convert( char(10) , convert( datetime,
DateLikeCharColumn ) , 110 ) , "" )
from mytable
join inserted on mytable.keycolumn = inserted.keycolumn

(substitute 110 with what ever style you need)

"nib" <individual_news@.nibsworld.com> wrote in message
news:304ot7F2qrbinU1@.uni-berlin.de...
> Myk wrote:
>> Hello All,
>>
>> None of the solutions I have found in the archives seem to solve my
>> problem. I have a date column in my tables (stored as a char(10))
> No, you have a char() column in your table that you store a string in that
> is supposed to represent a date. It may or may not.
>> which I would like to append a leading zero to for those dates that
>> start with 9 or lower.
> I'd recommend you actually make the column a date data type since that's
> what it is for, but you can append like this:
> CASE WHEN LEFT(YourColumn, 1) BETWEEN '1' AND '9' THEN '0' + YourColumn
> END
> Now, there are tons of things wrong with what I just wrote in that it
> assumes the first column will always be 0-9, which, given your specs
> above, isn't garunteed. It assumes that there are no spaces in the first
> character position. It assumes that if there is a 1 through 9 that the
> length of that string plus the new '0' is still within 10 chars. All in
> all, it is a crappy solution.
> Make your data column type match your data and this problem goes away.
> Zach
>>
>> Any ideas?
>>
>> Thanks,
>>
>> Mike
>

Add Date to Report Name and add server name too

Hi

I am new to MS Reporting Services so do forgive the noobish question.

I am trying to add a date to the emailed excel file. Is there someway to do this? I have schedules a daily file, eg file ReportDocument.xls. Is there someway to make it ReportDocument10/06/2006, ReportDocument10/07/2006, ReportDocument10/07/2006 etc? I've been looking around and I can't find an answer to this.

Any help would be appreciated. Thanks

Thanks

Adrian

You could just enter the date/time the file was created in the subject line. Would that be enough?

Subject = @.ReportName was executed at @.ExecutionTime

|||

Thanks

But I was referring to the excel file attachment name being appended with a date, not the subject line with date being appended. I've looked through the MSDN forum and apparently no one has an answer to this.

|||

Hi,

Besides Subject = @.ReportName was executed at @.ExecutionTime is there any other constants that I could use in the subject text box? I tried @.ServerName and @.ReportServerUrl, it did not work.

Thanks,

Philippe

|||

Sorry Adrian, but I don't think this functionality exists in the current version of Reporting Services with the email subscription.

You could setup a file share subscription and have an application that is polling the folder for new files.

Hope this helps.

Jarret

|||How can I extract just the date (and not time) from @.ExecutionTime.

Thanks,
Ashwin

Add Date to Report Name

Hi

I am new to MS Reporting Services so do forgive the noobish question.

I am trying to add a date to the emailed excel file. Is there someway to do this? I have schedules a daily file, eg file ReportDocument.xls. Is there someway to make it ReportDocument10/06/2006, ReportDocument10/07/2006, ReportDocument10/07/2006 etc? I've been looking around and I can't find an answer to this.

Any help would be appreciated. Thanks

Thanks

Adrian

You could just enter the date/time the file was created in the subject line. Would that be enough?

Subject = @.ReportName was executed at @.ExecutionTime

|||

Thanks

But I was referring to the excel file attachment name being appended with a date, not the subject line with date being appended. I've looked through the MSDN forum and apparently no one has an answer to this.

|||

Hi,

Besides Subject = @.ReportName was executed at @.ExecutionTime is there any other constants that I could use in the subject text box? I tried @.ServerName and @.ReportServerUrl, it did not work.

Thanks,

Philippe

|||

Sorry Adrian, but I don't think this functionality exists in the current version of Reporting Services with the email subscription.

You could setup a file share subscription and have an application that is polling the folder for new files.

Hope this helps.

Jarret

|||How can I extract just the date (and not time) from @.ExecutionTime.

Thanks,
Ashwin

Add Date to Report Name

Hi

I am new to MS Reporting Services so do forgive the noobish question.

I am trying to add a date to the emailed excel file. Is there someway to do this? I have schedules a daily file, eg file ReportDocument.xls. Is there someway to make it ReportDocument10/06/2006, ReportDocument10/07/2006, ReportDocument10/07/2006 etc? I've been looking around and I can't find an answer to this.

Any help would be appreciated. Thanks

Thanks

Adrian

You could just enter the date/time the file was created in the subject line. Would that be enough?

Subject = @.ReportName was executed at @.ExecutionTime

|||

Thanks

But I was referring to the excel file attachment name being appended with a date, not the subject line with date being appended. I've looked through the MSDN forum and apparently no one has an answer to this.

|||

Hi,

Besides Subject = @.ReportName was executed at @.ExecutionTime is there any other constants that I could use in the subject text box? I tried @.ServerName and @.ReportServerUrl, it did not work.

Thanks,

Philippe

|||

Sorry Adrian, but I don't think this functionality exists in the current version of Reporting Services with the email subscription.

You could setup a file share subscription and have an application that is polling the folder for new files.

Hope this helps.

Jarret

|||How can I extract just the date (and not time) from @.ExecutionTime.

Thanks,
Ashwin

Add Date to Report Name

Hi

I am new to MS Reporting Services so do forgive the noobish question.

I am trying to add a date to the emailed excel file. Is there someway to do this? I have schedules a daily file, eg file ReportDocument.xls. Is there someway to make it ReportDocument10/06/2006, ReportDocument10/07/2006, ReportDocument10/07/2006 etc? I've been looking around and I can't find an answer to this.

Any help would be appreciated. Thanks

Thanks

Adrian

You could just enter the date/time the file was created in the subject line. Would that be enough?

Subject = @.ReportName was executed at @.ExecutionTime

|||

Thanks

But I was referring to the excel file attachment name being appended with a date, not the subject line with date being appended. I've looked through the MSDN forum and apparently no one has an answer to this.

|||

Hi,

Besides Subject = @.ReportName was executed at @.ExecutionTime is there any other constants that I could use in the subject text box? I tried @.ServerName and @.ReportServerUrl, it did not work.

Thanks,

Philippe

|||

Sorry Adrian, but I don't think this functionality exists in the current version of Reporting Services with the email subscription.

You could setup a file share subscription and have an application that is polling the folder for new files.

Hope this helps.

Jarret

|||How can I extract just the date (and not time) from @.ExecutionTime.

Thanks,
Ashwin

Add Date to Report Name

Hi

I am new to MS Reporting Services so do forgive the noobish question.

I am trying to add a date to the emailed excel file. Is there someway to do this? I have schedules a daily file, eg file ReportDocument.xls. Is there someway to make it ReportDocument10/06/2006, ReportDocument10/07/2006, ReportDocument10/07/2006 etc? I've been looking around and I can't find an answer to this.

Any help would be appreciated. Thanks

Thanks

Adrian

You could just enter the date/time the file was created in the subject line. Would that be enough?

Subject = @.ReportName was executed at @.ExecutionTime

|||

Thanks

But I was referring to the excel file attachment name being appended with a date, not the subject line with date being appended. I've looked through the MSDN forum and apparently no one has an answer to this.

|||

Hi,

Besides Subject = @.ReportName was executed at @.ExecutionTime is there any other constants that I could use in the subject text box? I tried @.ServerName and @.ReportServerUrl, it did not work.

Thanks,

Philippe

|||

Sorry Adrian, but I don't think this functionality exists in the current version of Reporting Services with the email subscription.

You could setup a file share subscription and have an application that is polling the folder for new files.

Hope this helps.

Jarret

|||How can I extract just the date (and not time) from @.ExecutionTime.

Thanks,
Ashwin

Add date to record in SQL server each time record is added

Hi

Can anyone advise me as to how I can add the date and time to 2 columns in the sql server database for each record that is added. I'd prefer not to use the webform. Can sql server add the date automatically to the row?

thanks

Sure, you can do it a number of ways. Add a datetime field to your table, and set it's default value to either GetDate() or GetUTCDate(). That's one approach, but it only works for inserts.

If you want to have it updated on every update, then you will need to implement that in a trigger, or modify your UPDATE statements to set the field to GetDate() or GetUTCDate().

|||

Cool, I'll try that.

I want it INSERTED once for every record so i think the first way is the way to go.

Tell me this: do i need to add any code to the web form or is it enough to add the field to the db table and set the values?

Thanks!!

|||if you add a datetime column to a table, and set it's default value to getdate(), then whenever you insert a row into that table, the new column will have the date and time that the row was inserted. You don't have to change any SQL Statements at all, unless you've specifically created a dynamic update statement that will now attempt to insert a value into the new column, or something similiar. In most cases (I'll guess 95%+) nothing needs be changed at all.|||Cool. That is just perfect. No change needed at all. Thanks for your help!!|||I tried using the getdate() as the default; however, the date always comes out to be 1/1/1900. Any advise?|||

Haywire:

I tried using the getdate() as the default; however, the date always comes out to be 1/1/1900. Any advise?

What's the data type of the column? It should be be datetime.

add date range filter - brand newbie to reporting services

I've used report wizard to create a report - one of the fields in the report is a date field. I simply want a From Date: and To Date: filter which enables a user to select a range of dates to filter results. I know how to do this in .Net, but am not sure how to achieve this in RS. If anyone could shed some light, or point me to an thread which describes a similar process, I'd appreciate it.

thx

If you are using Visual Studio then for the data source your query should be just like a stored procedure with a parm...

Select col1, col2

From tbl1

Where col1 = @.parm1

When you open the report in Report Manager it should have a space for you to type in the value.

Now if you are using Report Builder then I don't know as I'm here looking for that answer myself.

|||

If you are using the report builder you just put the following the filter column for the date field.

Between @.SDate and @.EDate

Then go to your report parameters (right click on the upper left corner of the layout)

Click on each parameter SDate and EDate and set the type to Date/Time. It defaults as a string.

Hope this helps.

Jen