SQL Table Data, Smalldatetime And Calendar Control

Jul 13, 2007

Inside my SQL 2005 database I have a SmallDateTime with the layout "mm/dd/yyyy hh:mm:ss AM/PM" and then on a selected date of a calendar control it returns the date with 12:00:00 AM which obviously doesn't match with my database's time.

So, I'm looking for a way to drop the time off completely (for the query purpose) the database date when running a select statement. I don't want to touch SQL table data by modifying it, I just want to be able to query sql table using calendar control. Does anyone now how to accomplish this? Thank you in advance. - Nietzky

View 5 Replies


ADVERTISEMENT

Using A Calendar Control In An SQL Report

May 15, 2008

We have developed several reports using VS 2005 and deployed them to our SQL 2005 reporting server. As most of our reports use date parameters we would like to incorporate a calendar control to allow users to select their date range as opposed to typing them in. I have done some reseach online but can only find vague references on how to do this. Has anyone out there been able to successfully deploy a calendar control onto an SQL report? Thanks!

View 2 Replies View Related

Calendar Control In A Report?

Oct 19, 2007

I have a need to create a report that contains a calander, i.e. last month in rows and columns . Any idea on how to accomplish this need?

View 9 Replies View Related

Filtering Calendar Control?

Feb 15, 2007

Hello,

Please, help with following issues.

I have 3 parameters in the report. When user selects 1st one from dataset, one of the fields gives me object creation date. Calendar control is supposed to be filtered to allow users select the dates greater than selected one.

How can I filter (disable dates in) calendar control?

Any suggestion/comment highly appreciated

View 2 Replies View Related

Calendar Control In Safari

Apr 3, 2008

SSRS 2005-

The Calendar control that is used to select dates on a parameterized report that runs on a report server has been disabled for Safari. Users must type the dates that they want to use. http://technet.microsoft.com/en-us/library/ms156511.aspx

Does anyone know the reason why this was disabled? Will/Can it be enabled? I have clients that desire this functionality. How do I "Make a Suggestion" for the next release?

Thanks!

View 1 Replies View Related

Calendar Control And Database Connection

Dec 11, 2005

Hi,I m trying to implement a calendar, where you can select certain dates and view list of events for that day. The list is generated from database. I found a script on the web that is quite similar to what i want to achieve. I tried to run this script, however I get an exeption saying:An error has occurred while establishing a connection to the server. .... error: 40 - Could not open a connection to SQL Server)This is the code:SqlConnection mycn;    SqlDataAdapter myda;    DataSet ds = new DataSet();    DataSet dsSelDate;    String strConn;    private void Page_Load(object sender, System.EventArgs e)    {        // Put user code to initialize the page here        strConn = "Data Source=localhost;Initial Catalog=pubs";                mycn = new SqlConnection(strConn);        myda = new SqlDataAdapter("Select * FROM EventsTable", mycn);        myda.Fill(ds, "Table"); //This where I get the exeption    }    protected void CalendarDRender(object sender, System.Web.UI.WebControls.DayRenderEventArgs e)    {        // If the month is CurrentMonth        if (!e.Day.IsOtherMonth)        {            foreach (DataRow dr in ds.Tables[0].Rows)            {                if ((dr["EventDate"].ToString() != DBNull.Value.ToString()))                {                    DateTime dtEvent = (DateTime)dr["EventDate"];                    if (dtEvent.Equals(e.Day.Date))                    {                        e.Cell.BackColor = System.Drawing.Color.PaleVioletRed;                    }                }            }        }        //If the month is not CurrentMonth then hide the Dates        else        {            e.Cell.Text = "";        }    }    private void Calendar1_SelectionChanged(object sender, System.EventArgs e)    {        myda = new SqlDataAdapter("Select * from EventsTable where EventDate='" +            Calendar1.SelectedDate.ToString() + "'", mycn);        dsSelDate = new DataSet();        myda.Fill(dsSelDate, "AllTables");        if (dsSelDate.Tables[0].Rows.Count == 0)        {            DataGrid1.Visible = false;        }        else        {            DataGrid1.Visible = true;            DataGrid1.DataSource = dsSelDate;            DataGrid1.DataBind();        }    }        
    Can some one tell me what is wrong with the code.Thank You
WATCH THIS SPACE

View 1 Replies View Related

Calendar Control In Wrong Format

Mar 3, 2008

Hi,

I'm running SSRS reports on my local server - having deployed them locally. The calendar control is rendering dates in US format, i.e. 03/31/2008 and I need the British format of 31/03/2008.

The weird thing is that when I access the reports using the link:

http://localhost/Reports/Pages/Report.aspx?ItemPath=%2fLON-SQL-S03%2fOpsSupportLee%2fFailed+Form+Reasons

then everything is fine. However a direct call (skipping Report Manager) such as:

http://cbada00272/reportserver?%2fLON-SQL-S03%2fOpsSupportLee%2fFailed+Form+Reasons

produces the US format. My Regional setting is using English UK and the server is British English. What on earth is going on here? By the way cbada00272 is synonymous with localhost.

Regards,
Lee

View 11 Replies View Related

Calendar Control And Web Page Refresh :(

Mar 26, 2008

Hello. Is there any way to stop the web page from refreshing when selecting a date from the calendar control in SSRS 2005? I've done some digging around, and the only 'fix' found so far is to set the default date values from a dataset; which I've done, but Im still seeing the same results. I have a Start and End date parameter, both defined as datetime parameters, neither have any dependencies on any other report parameters, no cascading between any parameters in the report....nothing; as generic as it comes. I see there is an onclick Javascript event firing everytime a date is selected from this control. Is this what is causing the page refresh? Is there no way to turn this behavior off?

Thanks

View 11 Replies View Related

Trouble With Calendar Control For Datetime Parameter

May 4, 2007

I'm creating a report with a Datetime parameter. There's no available values, so the calendar control comes out automatically when I run/preview the report. Now, when I select the date in my calendar control, say for example I select 2/5/07 (2nd of May) and run the report, it gives me data from 5th of Feb. Can anyone help me with this date format problem ???

Not sure if it's related to my problem, I did edit the language tag in the XML source code of my report to have en-AU so that the dates field is showing in the correct dd/mm/yyyy format.

View 2 Replies View Related

Calendar Control In Reporting Services 2000

Jan 11, 2007



Hi All



Is it possible to add datetime picker (Calendar Control)

in 2000 reporting services

Cheers



View 3 Replies View Related

Reporting Services :: SSRS - Calendar Control With Time Picker

Oct 21, 2015

I defined my input parameter as date/time but it doesn't have a time picker where we can accept user input.

The calendar is great but time picker is missing.

Is there any add-in or tweaks I can use to get that feature ?

The alternative solution which uses drop down with some preset values are not feasible for me. All I need is just the time picker similar to adjust time in Windows.

View 4 Replies View Related

The Conversion Of Char Data Type To Smalldatetime Data Type Resulted In An Out-of-range Smalldatetime Value

Mar 30, 2007

I am using Visual Studio 2005 and SQL Express 2005. The database was converted from MS Access 2003 to SQL Express by using the upsize wizard.



I would like to store the current date & time in a column in a table. This column is a smalldatetime column called 'lastlogin'.

The code I'm using is:



Dim sqlcommand As New SqlCommand _

("UPDATE tableXYZ SET Loggedin = 'True', LastLogin = GetDate() WHERE employeeID = '" & intEmployeeID.ToString & "'", conn)



Try

conn.Open()

sqlcommand.ExecuteNonQuery()

conn.Close()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try



This code works fine on my local machine and local SQL server. However at the client side this code results in the error as mentioned in the subject of this thread. I first used 'datetime.now' instead of 'getdate()', but that caused the same error. Then I changed the code to 'getdate()', but the error still remains.



The server at the client is running Windows Server 2000 UK . My local machiine is running WIndows XP Dutch.

Maybe the conversion from Dutch to UK has something to do with it. But this should be solved by using the 'Getdate()' function..... ?













View 1 Replies View Related

How To Put Value In Control If There Is No Data In Table.

Feb 22, 2008

i am trying to get value from a table and put the vale in label control but i have null data in the table. if i have null data then i want the label to have " 1 " as the value.
this is my code. when i run this i get error.  Operator '=' is not defined for type 'DBNull' and string "". Dim conn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
Dim cmd As SqlCommand = New SqlCommand("SELECT MAX(PageHit) From BusinessLog WHERE (BusinessID=@BusID)", conn)cmd.Parameters.AddWithValue("@BusID", strBusID)
conn.Open()
If cmd.ExecuteScalar = "" Then
lblHits.Text = "1"
Exit Sub
Else
lblHits.Text = cmd.ExecuteScalar()
Exit Sub
End If

View 3 Replies View Related

Grouping Data In Matrix Like Table Control

Jun 6, 2007

All,



Is it possible to Group data in a Matrix exactly similar to Table Control. For example, my table control would group data as follows:



Region Country City $ales

North America

USA

Chicago 4 MM

LA 10 MM

NYK 6 MM

Canada

Toronto 4 MM





while the matrix would display as:



North America USA Chicago 4MM

LA

NYK

Canada Toronto ...



Do you see the problem? The matrix is starting the subgroup at the same level as the parent group. How do I make a sub group start at the row below the parent group row in matrix just like in the table above?

View 5 Replies View Related

Performing A Data Load Using A Control Table

Aug 18, 2006

Does anyone have an example of performing a source to destination data load with another SQL Select Statement controlling source statement? What I would like to do is split up a huge data move by performing a loop on the source and modifying the source select "where" clause using values from a control table. I understand how to modify the source statement by using an expression statement with variables. Now I'm trying to figure out how to loop through a control table to drive the source task.

Any tips would be greatly appreciated,

Brent

View 1 Replies View Related

Issue With SSRS Report Exporting To Excel With The Matrix Control Inside The Table Control

Jan 27, 2008

Hi All,
I am placing a Matrix inside the table control for grouping requirements,but when we export the report to the Excel, the contents inside the table cell are ignored. Is there any way to get the full report exported, as per the Requirement.Please help me with this issue.

With Thanks
M.Mahendra

View 5 Replies View Related

Transact SQL :: Joining A Calendar Table And Employee Table?

Apr 20, 2015

I run into a problem when asking to show a query of employee vacation days.

table 1:
column1  is dates
e.g.
2015-01-01
2015-01-02
2015-01-03 
.
.
.
2015-12-31

table2:
employeeID
vacation_date
Tom
2015-01-03
Tom
2015-01-04
David
2015-01-04
John
2015-01-08
Mary
2015-01-012

My query output need to be:

2015-01-01
2015-01-02
2015-1-03
Tom
2015-01-04
Tom
2015-01-04
David
2015-01-05
2015-01-06
2015-01-07
2015-01-08
John
2015-01-09
2015-01-10
2015-01-11
2015-11-12
Mary

... etc... all the way to 2015-12-31

when i use left outer join, i only record one employee per date.

View 4 Replies View Related

Populate Calendar Table

Aug 3, 2007

Hello all,
I would like to populate a calendar table similar to the one used in the foodmart2005 sample database. In this table the day of the week, month, and year are recorded as well as additional data which I do not require.
If possible I would like to populate the table with 10 years of data starting with 2007.

Secondly would 10 years of data make the table to large?

Thank you for any thoughts you may have.

View 1 Replies View Related

SQL 2012 :: Sync Table With Calendar?

Nov 11, 2014

sync values from table to calender. I got SQL Server 2012 and Outlook 2013.

I want to know is there any way all appointments booked in table should sync with exchange server automatically.

View 5 Replies View Related

Create First Day Of Month Column In Calendar Table

Nov 12, 2007



Hello,

I'm trying to get my head around this, any help would be appreciated. I have a calendar table I've created for use with a billing report. It would be great to have a column that had the first day of the month for every row entry (so for every row representing a day in february, that row would have February 1st, in datetime format). I thought it would be easy to create and populate this column at first, but I'm finding it much more difficult. Does anyone know how to populate this column?

Thanks in advance,

Andy

View 4 Replies View Related

Populating A SQL Table With SmallDateTime Values

Mar 24, 2008

I have a table that I would like to update with the current dateTime of Today. The Table Field is called "DateTimeStamp" with a format of SmallDateTime -- I'm using VS 2008.
When I use the following command and then cmd.ExecuteNonQuery -- the table is correclty filled in with the exception of the DateTimeStamp field. Instead of 3/23/2008, I get the value 1/1/1900 ... 
SqlCommand cmd3 = new SqlCommand("UPDATE CameraPods SET CameraBlock1_ID = 98, CameraBlock2_ID = 99, Status = 1, DateTimeStamp = 3/3/2008");
any ideas?
    ---Jim

View 3 Replies View Related

Adding A New Data Flow Item Calendar Transformer

Sep 25, 2007



http://www.microsoft.com/downloads/details.aspx?familyid=e603bde7-44bb-409a-890f-ed94a20b6710&displaylang=en#top

I've downloaded this and installed it but i seems to fail to get this item into my data flow items list. i've read the readme.txt but i think the part where they explain the build is poorly explained.
For example - Place gacutil.exe (packaged with Visual Studio) on the system path. --> wtf

Anyone can help me get this component in my visual studio working ?

Thanks

View 15 Replies View Related

SQL Server 2012 :: Add Business Days To A Date Using Calendar Table

May 12, 2015

I have a date that I need to add 'n' number of business days to. I have a calendar table that has a 'IsBusinessDay' flag, so it would be good if I was able to use this to get what I need. I've tried to use the 'LEAD' function in the following way;

SELECT A. Date, B.DatePlus3BusinessDays
FROM TableA A

LEFT JOIN (Select DateKey, LEAD(DateKey,3) OVER (ORDER BY datekey) AS DatePlus3BusinessDays FROM Calendar WHERE IsBusinessDay = 1) B ON A.DateKey = B.DateKey

Problem with this is that because I am filtering the Calendar for business days only, when there is a date that is not a business day in TableA, a NULL is being returned.

Is there any way to do a conditional LEAD, so it skips rows that are not business days? Or do I have do go with a completely different approach?

View 9 Replies View Related

Transact SQL :: Calendar Table - Get The Date Post 3 Business Days

Sep 10, 2015

I have a calendar table against entire year 2015 with each day with 2 flag,

1. WK_DT_IN == except Satarday and Sunday, value is "Y", for Sat/Sun, value is "N"
2. HOL_DT_IN == value will only be "Y" only for holiday, example for '2015-01-01' date, it's value is "Y"

DECLARE @CAL TABLE (CAL_DT DATE, WK_DT_IN CHAR(1), HOL_DT_IN CHAR(1))
INSERT INTO @CAL VALUES ('2015-01-01', 'Y', 'Y'), ('2015-01-02', 'Y', 'N'),('2015-01-03', 'N', 'N'),
('2015-01-04', 'N', 'N'), ('2015-01-05', 'Y', 'N'), ('2015-01-06', 'Y', 'N'), ('2015-01-07', 'Y', 'N')

We have a given date, example '2015-01-01', I need to find out a date after 2 business days? I can think of loop, but will it work.

I need to exclude date which having HOL_DT_IN = "Y" and WK_DT_IN = "N".

View 3 Replies View Related

Working With Smalldatetime Data Type

Mar 11, 2004

Hello all,

if i save a date value within a smalldatetime field, then later want to edit the record and set the smalldatetime field to nothing, how can i do this. I've tried populating the field with Null, and anything else, without success. Any suggestions?

Sammy

View 2 Replies View Related

Power Pivot :: Calendar Look Up Table - Custom 24 Hour Start Time

Jul 9, 2015

I know how to do this in Excel, but not sure about Power Pivot. Our fire department works on a 24 hour shift that starts at 0700 each morning. I want to look up what shift is working depending on the date and time of dispatch. In Excel, I would do a look up table, but instead of using false() as  the last parameter, I would choose true().

I am not sure if this would be best created as a calculated column in the table that holds the incident date and time values, or have a separate table and create a relationship to use the shift in the rows/columns of my pivot table. 

View 6 Replies View Related

Transact SQL :: Calc Work Date Using Business Days Field In Calendar Table

May 19, 2014

I created a dbo.Calendar table that stores dates and a work day flag (1=work day, 0=non-work day) so I can use it to calculate the next business date from a date using a function. I'm using a while group to count only the work days and a couple other internal variables but I'm not sure if I can even use them in a function.

Assuming Sats & Suns are all non-work days in April 2014, if my @WorkDays = 10 for 10 work days and my @DateFromValue - 4/1/2014, I would expect my return date to be 4/15/2014.

------ Messages after I click execute on my query window that has my function ------------------------------------------------------
Msg 444, Level 16, State 2, Procedure FGetWorkDate, Line 19
Select statements included within a function cannot return data to a client.
Msg 207, Level 16, State 1, Procedure FGetWorkDate, Line 20
Invalid column name 'WorkDay'.
Msg 207, Level 16, State 1, Procedure FGetWorkDate, Line 22
Invalid column name 'Date'.

------ my function code ----------------------------
CREATE FUNCTION [dbo].[FGetWorkDate](
    @WorkDays VARCHAR(5),
    @DateFromValue AS DateTime )
    RETURNS DATETIME

[Code] ....

View 10 Replies View Related

Conversion Failed When Converting Character String To Smalldatetime Data Type

Oct 12, 2006

I am newbie in asp and sql, and I am using VS & SQL expresswhen I try to submit I get following error"Conversion failed when converting character string to smalldatetime data type"Following is my insert statement<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:oncallConnectionString %>"SelectCommand="SELECT data.* FROM data" InsertCommand="INSERT INTO data(Apps, Location, Impact, System, Date, Start_Time, End_Time, Duration, Problem, Cause, Solution, Case, Comments) VALUES ('@DropDownList1','@DropDownList2','@DropDownList3','@TextBox6','@DropDownCalendar1','@DropDownCalendar2','@DropDownCalendar3','@TextBox1','@TextBox2','@TextBox3','@TextBox4','@TextBox5','@TextBox7')"></asp:SqlDataSource>These are @DropDownCalendar1','@DropDownCalendar2','@DropDownCalendar3' defined as datetime in database.I would appriciate if somebody could help.

View 7 Replies View Related

Conversion Failed When Converting Character String To Smalldatetime Data Type.

Mar 25, 2007

Hello, I have problem with this code.(This program presents - there is GridView tied to a SQL database that will sort the data selected by a dropdownList at time categories. There are  2  time categories in DropDownList - this day, this week.
 Problem: when I choose one categorie in dropDownlist for examle this week and submit data on the server I got this error.
Conversion failed when converting character string to smalldatetime data type.
Here is code:
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
 
 
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
 
string datePatt = @"yyyymmdd";
// Get start and end of day
DateTime StartDate = DateTime.Today;
 
string @StartDate1 = StartDate.ToString(datePatt);
 
string @EndDate = StartDate.AddDays(1).ToString(datePatt);
// Get start and end of week
string @startOfWeek = StartDate.AddDays(0 - (int)StartDate.DayOfWeek).ToString(datePatt);
string @startOfNextWeek = StartDate.AddDays(7 - (int)StartDate.DayOfWeek).ToString(datePatt);
 
 
switch (DropDownList1.SelectedValue)
{
 
case "1":
// day
SqlDataSource1.SelectCommand = "SELECT [RC_USER_ID], [DATE], [TYPE] FROM [T_RC_IN_OUT]" + "WHERE" + "[DATE] >=" + "'@StartDate1'" + " AND [DATE] < " + "'@EndDate'";
break;
case "2":
//week
SqlDataSource1.SelectCommand = "SELECT [RC_USER_ID], [DATE], [TYPE] FROM [T_RC_IN_OUT]" + "WHERE" + "[DATE] >=" + "'@startOfWeek'" + "AND [DATE] <" + "'@startOfNextWeek'";
break;
 
}
 
}
 
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Untitled Page</title>
<style type="text/css">
body {
font: 1em Verdana;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
&nbsp;&nbsp;
 
<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged"
Style="z-index: 100; left: 414px; position: absolute; top: 22px">
<asp:ListItem Selected="True" Value="1">jeden den</asp:ListItem>
<asp:ListItem Value="2">jeden tyden</asp:ListItem>
</asp:DropDownList>
 
<asp:GridView ID="GridView1" runat="server" Style="z-index: 102; left: 228px; position: absolute;
top: 107px" DataSourceID="SqlDataSource1" AutoGenerateColumns="True">
 
</asp:GridView>
&nbsp; &nbsp;<br/> <br/>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=CRSQLEXPRESS;
Initial Catalog=MyConn;Integrated Security=True"
ProviderName="System.Data.SqlClient"></asp:SqlDataSource>
</div>
</form>
</body>
</html>
 

View 4 Replies View Related

Conversion Failed When Converting Character String To Smalldatetime Data Type

Oct 3, 2007

I am running the following query:
 select distinct hqvend, hqvprd,fprod,idesc,ilead,sum(cast(fqty as int) )as fqty, (cast(hqvend as varchar(5))+'/'+ltrim(rtrim(fprod))+'/'+cast(ilead as varchar(3))) as Keydata,'L1/'+rtrim(fprod) as LocPartno from tblkfpl01 inner join hqtl01 on (fprod=hqprod) inner join iiml01 on (hqvend=ivend and hqprod=iprod) where  cast(cast(hqeff as varchar(8)) as smalldatetime) <= (Select CONVERT(varchar(8), getdate(), 1)) and  cast(HQDIS as varchar(8)) >= (Select CONVERT(varchar(8), getdate(), 1))  and hqvend like '134%' group by hqvend,fprod,hqvprd,idesc,ilead   order by hqvend,fprod
The bold sections are giving me the error message. The query works as written above, butis only evaluating on one date, contained in hqeff. However, when I try to modify the second date, HQDIS, by casting it the same way as the one before it:
 select distinct hqvend, hqvprd,fprod,idesc,ilead,sum(cast(fqty as int) )as fqty, (cast(hqvend as varchar(5))+'/'+ltrim(rtrim(fprod))+'/'+cast(ilead as varchar(3))) as Keydata,'L1/'+rtrim(fprod) as LocPartno from tblkfpl01 inner join hqtl01 on (fprod=hqprod) inner join iiml01 on (hqvend=ivend and hqprod=iprod) where  cast(cast(hqeff as varchar(8)) as smalldatetime) <= (Select CONVERT(varchar(8), getdate(), 1)) and  cast(cast(HQDIS as varchar(8)) as smalldatetime) >= (Select CONVERT(varchar(8), getdate(), 1))  and hqvend like '134%' group by hqvend,fprod,hqvprd,idesc,ilead   order by hqvend,fprod
I get the error message. I need to select based on both dates (hqeff AND HQDIS), but cannot seem to be able to it.... Both fields are of type Dedimal, with a length of 8, and dates stored in them look like: 20071003 (YYYYMMDD).
Any suggestions?

View 2 Replies View Related

Syntax Error Converting Character String To Smalldatetime Data Type.

Aug 8, 2005

Hi guys and gals,I have the following code:CREATE PROCEDURE searchRecords(     @searchFor  NVarchar (25)  = NULL,     @fromDate  NVarchar (25)  = NULL,     @toDate  NVarchar (25)  = NULL)
AS
IF @fromDate IS NOT NULL     DECLARE @fromDateString VarChar(200)     SET @fromDateString = '  CONVERT(smalldatetime, ''' + @fromDate + ''') '
IF @toDate IS NOT NULL     DECLARE @toDateString VarChar(200)     SET @toDateString = '  CONVERT(smalldatetime, ''' + @toDate + ''') '
SELECT * FROM MIPR WHERE      ID = @searchFor      OR DESC_QTY = @searchFor      OR REQ_ACTIVITY = @searchFor      OR ACC_ACTIVITY = @searchFor      OR MANYEARS = @searchFor      OR TECH_POC = @searchFor      OR RESOURCE_POC = @searchFor      OR SI_DATE BETWEEN @fromDateString   AND  @toDateString;GOit creates an error that I can't seem to fix. the error I get is:Syntax error converting character string to smalldatetime data type.How can I fix this? Thanks in advance!

View 1 Replies View Related

Conversion Failed When Converting Character String To Smalldatetime Data Type

Oct 21, 2007

Hi,

I have SQL Server 2005 database table with the following data definition as follows:

ID int
LST_TIME varchar(5)
LST_DATE varchar(21)
LST_WEEK int
SUBJECT varchar(100)


Date format (Month,day YEAR) eg.- October, 21 2007
Time format, 00 - 23 hours, HH:MM eg. - 18:58

, and a VB code as below:

Dim Command As New SqlClient.SqlCommand("SELECT * FROM dbo.LISTS WHERE (LST_WK = DATEPART(wk, GETDATE())) AND (CONVERT(datetime, LST_DATE) = CONVERT(datetime, CONVERT(varchar, GETDATE(), 110)))
AND(CONVERT(smalldatetime, LST_TIME) = CONVERT(smalldatetime, CONVERT(varchar(5), GETDATE(), 108)))", conn)

Dim dataReader As SqlClient.SqlDataReader = Command.ExecuteReader(CommandBehavior.CloseConnection)
While dataReader.Read()

Dim NewAlert As New Alert(dataReader("SUBJECT").ToString(), DateTime.Parse(dataReader("LST_DATE").ToString(),
DateTime.Parse(dataReader("LST_TIME").ToString())))

:
:
:
I am encountering error: System.Data.SqlClient.SqlException: Conversion failed when converting character string to smalldatetime data type, which points to the codes in bold. Just wondering anyone out there has got a solution to my problem. Thank you.

View 5 Replies View Related

Conversion Failed When Converting Character String To Smalldatetime Data Type

Apr 10, 2008

Recently i traspased a database from sql server 2000 in spanish to sql server 2005 in english. I change the language of the user sa to spanish and the database is in spanish. . The problem is our dateformat is dmy, i try to force with set dateformat dmy, but always i execute a stored procedure that have a date as input it fail. The error is:

Msg 295, Level 16, State 3, Procedure pa_CalendarioGestion, Line 40
Conversion failed when converting character string to smalldatetime data type.

The code of stored procedure in this line is:

set @diaActual = dateadd("d", @i, @fechaInicio)

This stored procedure in sql server 2000 in spanish not have any problems. For this reason i think the problem is in the configuration.

I hope someone can help me. Thanks.

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved