Date Problem With ASP.NET But Not In VB.NET Winform App
Sep 22, 2004
Edited by SomeNewKid. Please post code between <code> and </code> tags.
Hello, I have given up after 3 hours of trying to get a DBNull.Value to be inserted into SQL 2000 DB. Below is the code that exists in an object that I use both from a VB.NET Windows application and from an ASP.NET application. (same exact compiled object dll). Has ran fine with windows application for almost a year - still does. Use the same object in my new ASP.NET application and I get a SQL Overflow error that says date must be between 1753 12 am etc etc... when trying to insert null using the code below.
This one has me stumped. The lines below where I set the date are basically this (what the logic equates to):oDR.Item("CT_CustomDate1") = DBNull.Value
oDR.Item("CT_CustomDate2") = DBNull.Value
Error gets thrown when this line is executed:oDA.Update(oDT)
Also, the CT_CustomDate1 & 2 fields in the SQL Server 2000 DB are of type SmallDateTime and the variables in object used in code function below are Date. I've tried every combination of Date, DateTime, SmallDateTime, etc. etc. to no avail.
Thanks for you help!
Public Function AddNew(ByVal sGUID As String) As Boolean
'Saves all new contact information to database
Dim sSQL As String
sSQL = "SELECT * FROM Contact WHERE 1=2"
Dim oConn As New SqlConnection(sConnectionString)
Dim oDT As New DataTable
Dim oDA As New SqlDataAdapter(sSQL, oConn)
Dim oDR As DataRow
Dim bSuccess As Boolean
Try
oDA.Fill(oDT)
Dim dataCommandBuilder As New SqlCommandBuilder(oDA)
oDA.InsertCommand = dataCommandBuilder.GetInsertCommand
oDA.DeleteCommand = dataCommandBuilder.GetDeleteCommand
oDA.UpdateCommand = dataCommandBuilder.GetUpdateCommand
oDR = oDT.NewRow
oDR.Item("CT_GUID") = sGUID
oDR.Item("CT_CompanyGUID") = CTCompanyGUID
oDR.Item("CT_Prefix") = CTPrefix
oDR.Item("CT_FirstName") = CTFirstName
oDR.Item("CT_MiddleName") = CTMiddleName
oDR.Item("CT_LastName") = CTLastName
oDR.Item("CT_Suffix") = CTSuffix
oDR.Item("CT_Title") = CTTitle
oDR.Item("CT_ContactAddress") = CTContactAddress
oDR.Item("CT_Address1") = CTAddress1
oDR.Item("CT_Address2") = CTAddress2
oDR.Item("CT_City") = CTCity
oDR.Item("CT_State") = CTState
oDR.Item("CT_Zip") = CTZip
oDR.Item("CT_Country") = CTCountry
oDR.Item("CT_InternationalPhone") = CTInternationalPhone
oDR.Item("CT_Phone") = CTPhone
oDR.Item("CT_Fax") = CTFax
oDR.Item("CT_Cell") = CTCell
oDR.Item("CT_AltPhone") = CTAltPhone
oDR.Item("CT_Assistant") = CTAssistant
oDR.Item("CT_AssistantPhone") = CTAssistantPhone
oDR.Item("CT_NoEmail") = CTNoEmail
oDR.Item("CT_EmailAddress") = CTEmailAddress
oDR.Item("CT_Confidential") = CTConfidential
oDR.Item("CT_TypeGUID") = CTTypeGUID
oDR.Item("CT_Designation") = ""
oDR.Item("CT_LastUpdate") = CTLastUpdate
oDR.Item("CT_UpdatedByWho") = CTUpdatedByWho
oDR.Item("CT_Location") = CTLocation
oDR.Item("CT_CustomBit1") = CTCustomBit1
oDR.Item("CT_CustomBit2") = CTCustomBit2
oDR.Item("CT_CustomBit3") = CTCustomBit3
oDR.Item("CT_CustomBit4") = CTCustomBit4
oDR.Item("CT_CustomBit5") = CTCustomBit5
oDR.Item("CT_CustomBit6") = CTCustomBit6
oDR.Item("CT_CustomBit7") = CTCustomBit7
oDR.Item("CT_CustomStr1") = CTCustomStr1
oDR.Item("CT_CustomStr2") = CTCustomStr2
oDR.Item("CT_CustomStr3") = CTCustomStr3
oDR.Item("CT_CustomStr4") = CTCustomStr4
oDR.Item("CT_CustomStr5") = CTCustomStr5
oDR.Item("CT_CustomStr6") = CTCustomStr6
oDR.Item("CT_CustomStr7") = CTCustomStr7
oDR.Item("CT_CustomStr8") = CTCustomStr8
oDR.Item("CT_CustomStr9") = CTCustomStr9
oDR.Item("CT_CustomStr10") = CTCustomStr10
oDR.Item("CT_CustomStr11") = CTCustomStr11
oDR.Item("CT_CustomStr12") = CTCustomStr12
oDR.Item("CT_CustomStr13") = CTCustomStr13
oDR.Item("CT_CustomPhone1") = CTCustomPhone1
oDR.Item("CT_CustomPhone2") = CTCustomPhone2
oDR.Item("CT_CustomPhone3") = CTCustomPhone3
oDR.Item("CT_CustomPhone4") = CTCustomPhone4
oDR.Item("CT_CustomDate1") = IIf(CTCustomDate1 = #12:00:00 AM#, DBNull.Value, CTCustomDate1)
oDR.Item("CT_CustomDate2") = IIf(CTCustomDate2 = #12:00:00 AM#, DBNull.Value, CTCustomDate2)
'oDR.Item("CT_CustomDate1") = CDate(#1/1/1753#)
'oDR.Item("CT_CustomDate2") = CDate(#1/1/1753#)
oDR.Item("CT_CustomAmount1") = CTCustomAmount1
oDR.Item("CT_CustomAmount2") = CTCustomAmount2
oDR.Item("CT_CustomType1GUID") = CTCustomType1GUID
oDR.Item("CT_CustomType2GUID") = CTCustomType2GUID
oDR.Item("CT_CustomCompany1GUID") = IIf(CTCustomCompany1GUID = "00", DBNull.Value, CTCustomCompany1GUID)
oDR.Item("CT_CustomCompany2GUID") = IIf(CTCustomCompany2GUID = "00", DBNull.Value, CTCustomCompany2GUID)
oDR.Item("CT_CustomContact1GUID") = IIf(CTCustomContact1GUID = "00", DBNull.Value, CTCustomContact1GUID)
oDT.Rows.Add(oDR)
oDA.Update(oDT)
CTGUID = sGUID
bSuccess = True
Catch err As Exception
MsgBox("Error saving new contact..." & vbCrLf & vbCrLf & err.Message, MsgBoxStyle.Critical + MsgBoxStyle.OKOnly, "Database Error")
bSuccess = False
End Try
AddNew = bSuccess
End Function
View 1 Replies
ADVERTISEMENT
Nov 28, 2006
Is there a way to make the data selecatable. For example, a user runs a report and wants to copy the applicationId onto their clipboard so they can use it to search in another application. Currently they have to remember or write down a 9 digit number. Not very user friendly...
I am currently using a data table as the container for the data in my rdl file. Then I use reportviewer for winforms to embed it in our user application.
Thanks
View 3 Replies
View Related
Nov 14, 2005
Hi All,
I have a winform app built using vb.net that utilize SQL 2005. How do i include the database that i have within the application itself and how does that affect the connection string? This lead to the 2nd question, Will the database have multi user capability if i set the connection string to (local)? Does anybody have a step by step way to do this? Please elaborate the way to do it or share with us the link to do it.
Thanks in advance!
hwdevelop
View 2 Replies
View Related
May 28, 2008
I have asked this question before (but the question was geared for something else). Let me briefly lay out my question.
I currently have a WinForm app I distribute that interfaces with Access. When I distribute it, I have in the application startup path a folder called "Database" which has the Access .mdf. With me so far?
Now.
I have tools that automate and semi-automate the embedding of important information into my database during the course of a project. However, because more people are starting to use my tools, we have a concurrency issue. I was looking at SSEE as a replacement for Access to solve this problem and avoid the concurrency problem. Hence it is not for the customer I am moving to SSEE but for my internal team who use my tools.
I am having a difficult time delivering the finished product because I don't quite know how to deliver SSEE. I am very proficient with setups, and know that as a prequisite they have SSEE as a selectable item, but how to I attach my database to SSEE in the setup? Do I need to detach my database from my server and reattach it during the setup for the customer?
I have found no articles describing this (except ClickOnce which is not what I want). If everyone raves about SSEE over Access, I would think this would be a widely available question.
This is probably a question for a MVP or someone who has dealt with this personally, so I would welcome your expertise in this matter.
Thanks
View 7 Replies
View Related
Mar 24, 2007
I have created Reports using SQL Server 2005 Reporting Service.
Now I want to print them in button click on Windows Form without displaying or previewing it.
More over I want to print 2 copys at a time?
I want to print without preview.
Nilesh
View 1 Replies
View Related
Mar 22, 2008
Im trying to figure out if there is a way to embed an ssis package into a c# winform?
I know that I can pass variables to a package and write the entire package out programatically, but im curious if there is a way to add the dtsx as an assembly or something. OR is there any tool that would take the ssis package and spit out its equivalent in code?
thanks
View 6 Replies
View Related
Sep 27, 2007
I saw a video on how its possible to create a local database so that when the application is being deployed, the mdf file will be deployed with it and will contain the enter data. The instructor(Beth Messi) showed that all I need to do is to add the .mdf file using the "Rightclick Project name in solution explorer > select add > add new item > then in the dialog that opens, select SQL database and click ok. I did this but the Visual Studio kept saying: "An error occored while extablishing a connection with the server. When connecting SQL Server 2005, this failure may have been caused by the fact that under the default settings of SQL Server does not allow remote connections. (Provider: Shared memory provider, error: 40 - could not open a connection to SQL Server)"
Honestly, I don't know how to go futher with this. The SQL server am using is the professional edition and I have been able to use it through Visual Studio to create Databases. It connects alright in that senario. Please I really need help here.
View 1 Replies
View Related
Nov 7, 2006
In order to provide runtime, realtime, graphical feedback to ETL operators, I am planning to open a new winform when my packages start, and update the form via polling of events.
It is my understanding that this means I need to create a new thread for the winform from within the script task. Anyone done this via script task yet? Any reason why this couldn't work or shouldn't be done from the script task?
Would I be better off writing the winform app completely seperately and simply call it from an "execute process" task?
Thanks,
Ken
View 3 Replies
View Related
Sep 21, 2006
HiI am using SQL 2005, VB 2005I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works finestring insertSQL; insertSQL = "INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@ProjectID, @TypeofEntryID, @PriorityID ,@Title, @Area)"; cmdInsert SqlCommand; cmdInsert=new SqlCommand(insertSQL,conn); cmdInsert.Parameters.Add("@ProjectID",SqlDbType.Varchar).Value=ProjectID.Text; My query is how to detail with dates my previous code wasinsertSQL += "convert(datetime,'" + DateTime.Now.ToString("dd/MM/yy") + "',3), '";I tried the code below but the record doesn't save?string date = DateTime.Now.ToString("dd/MM/yy"); insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@IDIssue, @IDTask, @TaskDone, convert(DateTime,@Date,3),@IDStaff)"; cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;Could someone point to me in the right direction?Thanks in advance
View 3 Replies
View Related
Jun 13, 2002
In SQL Server 2000:
How do I convert a Julian date to a Gregorian date?
How do I convert a Gregorian date to Julian?
Examples please.
Many thanks in advance.
Gary Andrews
View 2 Replies
View Related
Jul 29, 2015
My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see
Select
Jan 1- 23rd
feb 1-23rd
march 1-23rd
april 1-23rd
,value
from
table
View 9 Replies
View Related
Mar 20, 2015
I am trying to find a beginning date from multiple date ranges, for example:
RowNumberidBegin dtEnd Dt
107933192014-09-022015-06-30
207933192013-09-032014-09-01
307933192012-09-042013-09-02
407933192011-09-062012-09-03
For this id: 0793319, my beginning date is 2011-09-06
108203492014-09-022015-06-30
208203492013-09-032014-09-01
308203492012-09-042013-09-02
408203492011-12-122012-07-03--not a continuous date range
For this id: 0793319, my beginning date is 2012-09-04
108203492014-09-022015-06-30
For this id: 0820349, my beginning date is 2014-09-02
To find continuous date, you look at the beginning date in row 1 and end date in row 2, then if no break in dates, row 2 beginning date to row 3 end date, if no break continue until last date There could multiple dates up to 12 which I have to check for "no break" in dates, if break, display beginning date of last continuous date.
View 9 Replies
View Related
May 30, 2015
I want to compare two columns in the same table called start date and end date for one clientId.if clientId is having continuous refenceid and sartdate and enddate of reference that I don't need any caseopendate but if clientID has new reference id and it's start date is not continuous to its previous reference id then I need to set that start date as caseopendate.
I have table containing 5 columns.
caseid
referenceid
startdate
enddate
caseopendate
[code]...
View 4 Replies
View Related
Mar 18, 2014
I have the following
Column Name : [Converted Date]
Data Type : varchar(50)
When I try and do month around the [Converted Date] I get the following error message
“Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.”
My Query is
SELECT
month([Created Date])
FROM [FDMS_PartnerReporting].[Staging].[Salesforce_MarketingReporting]
View 7 Replies
View Related
Jul 20, 2005
Hi,I have a problem with updating a datetime column,When I try to change the Column from VB I get "Incorrect syntax near'942'" returned from [Microsoft][ODBC SQL Server Driver][SQL Server]'942' is the unique key column valueHowever if I update any other column the syntax is fineThe same blanket update query makes the changes no matter what isupdatedThe problem only happens when I set a unique key on the date field inquestionKey is a composite of an ID, and 2 date fieldsIf I allow duplicates in the index it all works perfectlyI am trying to trap 'Duplicate value in index' (which is working onother non-date columns in other tables)This is driving me nutsAny help would be appreciated
View 5 Replies
View Related
Apr 30, 2015
So I have to build dynamic T-SQL because of a date parameter that will be provided. The Date Parameter will be provided in SSRS in normal MM/DD/CCYY format. So how do I then convert that date to my Oracle format
NUMERIC(8,0) CCYYMMDD?
I tried this...
SET@SQLQuery=@SQLQuery+'ANDMEMBER_SPAN.YMDEFF<='''''+CAST(@AsOfDateASVARCHAR)+''''''+@NewLineChar;
SET@SQLQuery=@SQLQuery+'ANDMEMBER_SPAN.YMDEFF>='''''+CAST(@AsOfDateASVARCHAR)+''''''+@NewLineChar;
but that put it in the format of...
AND
MEMBER_SPAN.YMDEFF<=''2015-04-01''
AND
MEMBER_SPAN.YMDEFF>=''2015-04-01''
Which is close...I think I just need to lose the "-"
View 5 Replies
View Related
Nov 16, 2015
SELECT * ,[Due]
FROM [Events]
Where Due >= getdate() +90
This returns the error: Conversion failed when converting date and/or time from character string
Why would this be? How to cast or convert this so that it will work?
View 24 Replies
View Related
Sep 28, 2007
Using DTS package in 2000 version, I am dumping TXT file contents into SQL Table,
I have one column having date in format YYYYMMDD(20070929) and corresponding column in SQL is datetime, but it fails on data type mismatch.
I have no choice of making date column in SQL to string or Varchar etc,
is there any way to make that date column in SQL to convert the value upon transformation from format (YYYYMMDD) to M/DD/YYYY (9/29/2007).
many many thanks,
View 2 Replies
View Related
Mar 12, 2008
Hi,
Basically the above is a very common requirement, please comment on my solution which I've arrived at by searching through the web; -
In summary I have used 3 SSIS components these are "Flat File Source", "Derived Column" and "SQL Server Destination".
1) File Connections Manager Editor
1.1) Within File Connections Manager Editor; -
Name the data type e.g. "INTERCHANGE_NET_APP_DATE_SRC"
and assign a type to the data type e.g. string[DT_STR]
1.2) Click on the Preview button to ensure the expected text is assigned to the expected data type.
2) Derived Column Transformation Editor
2.1) Assign Derived Column Name, e.g.
INTERCHANGE_NET_APP_DATE
2.2) Select <add as new column> within Derived Column.
2.3) Enter the conversion Expression, e.g. ; -
2.3.1)
(SUBSTRING(INTERCHANGE_NET_APP_DATE_SRC,8,2) + "/" + SUBSTRING(INTERCHANGE_NET_APP_DATE_SRC,5,2) + "/" + SUBSTRING(INTERCHANGE_NET_APP_DATE_SRC,1,4))
2.3.2)
Since the above conversion is such a common task I suggest that Service Pack 3 of SQL Server 2005 delivers the following functionality; -
STRINGTODATE ('YYYYMMDD',INTERCHANGE_NET_APP_DATE_SRC)
2.4) Select "database timestamp [DT_DBTIMESTAMP] " as Data Type.
2.5) Within the Mappings tab of the SQL Destination Editor have; -
Input Column as INTERCHANGE_NET_APP_DATE and
Destination Column as INTERCHANGE_NET_APP_DATE.
Please comment on the above, I will then pass on my suggestion to Microsoft.
Thanks in advance,
Kieran.
View 1 Replies
View Related
Nov 13, 2007
Hi there.
I'm trying to extract data from my SQL server & everything in the script I've got is working (extracting correct data) except for one field - which is for the most part it's off by +2 days (on a few occasions - I see it off by just +1 day or even +3, but it's usually the +2 days).
I'm told that it's due to the conversion formula - but - since SQL is not my native language, I'm at a bit of a loss.
The DB table has the date field stored as a type: CHAR (as opposed to 'DATE')
Can anyone out there help?
Please advise. Thanks.
Best.
K7
View 1 Replies
View Related
Nov 15, 2007
Hi, I really need this help urgently.
I need to send an email when the dueDate(field name in database) is equal to today's date... I have come out with this code with the help of impathan(jimmy i did not use ur code cos i not very sure sry)... below is the code with no error... but it jus wun send email...
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load, Me.Load
con1.Open()
Dim cmd As New SqlCommand
cmd.CommandText = "select * from custTransaction where convert(datetime,dueDate,101) = convert(datetime,GetDate(),101)"
'Set the connect the command object should use
cmd.Connection = con1Dim da As New SqlDataAdapter(cmd)Dim ds As New DataSet
da.Fill(ds)
con1.Close()
If Not ds.Tables(0) Is Nothing ThenIf ds.Tables(0).Rows.Count > 0 Then
Dim objEmail As MailMessage = New MailMessage
objEmail.From = New MailAddress("my@email.com.sg")objEmail.To.Add(New MailAddress("my@email.com.sg"))
objEmail.Subject = "Due Date Reaching"objEmail.Body = Session("dueName")
objEmail.Priority = MailPriority.Normal
Dim SmtpMail As New SmtpClient("servername")
SmtpMail.Send(objEmail)
End If
End If
End Sub
Note: I am veri sure that database has the data field dueDate with the value 11/16/2007 smalltimedate(mm/dd/yyyy)
Realli veri urgent Thanks so much for ur'll help
View 8 Replies
View Related
Jan 11, 2006
hello
how can i select all dates upto todays date and include the first next future date in a given data base
say todays date was the 01/06/2006 (MM,DD,YYYY)
below is a mock data base
id date (MM,DD,YYYY)
1 01/02/2006
2 01/04/2006
3 01/06/2006
4 01/09/2006
5 01/20/2006
i want to select all dates equal or less that 01/06/2006 and include the first next future date .. and in this case it would be 01/09/2006
so the results would return
1 01/02/2006
2 01/04/2006
3 01/06/2006
4 01/09/2006
View 2 Replies
View Related
Aug 24, 2015
PHP Code:
INSERT INTO [GPO].dbo.tblMetric (KPI_ID, METRIC_ID, GOAL, REPORTING_MONTH, ACTUALS)
SELECT
1 AS KPI_OWNER_ID
, 23 AS METRIC_ID
, .75 AS GOAL
, CAST(Z.REPORTING_MONTH as DATE) AS REPORTING_MONTH
, SUM(CAST(FTP_COUNT AS DECIMAL))/SUM(CAST(FULL_COUNT AS DECIMAL)) AS ACTUALS
[Code] ....
The insert column I am trying to get into is a date type. The original state of the field is YYYYMM varchar. How to get this into the table.
View 3 Replies
View Related
Jan 30, 2007
I asked this question below, but the answer was that the conversion will take place automatically, but I can't get that to happen. I have a flat file with an 8 position field that I identify as string (and I also tried date) that is yyyymmdd and it needs to go into the database field that is datetime format. IS there something I am doing wrong with the definition of it, or do I need to add some kind of conversion, and if so, what and how would that be done. I'm a dts Sql2000 expert, but the SSIS thing is driving me crazy. I have a ton of dts' to convert and the migration tool doesn't work because there are a lot of active X scripts in them. thanks for your help. Boston Rose
View 1 Replies
View Related
Sep 5, 2007
Dear Expert!
A server with SQL 2005 sp2, Reporting Services and Sharepoint services (ver 3.0) (in integrated mode) gives an odd error. When viewing a Reporting Services report with a Date Time Picker, the date chosen is wrong. The preferred setting is Danish with the date format dd-mm-yyyy. The date picker shows the months in Danish but when selecting a date, and clicking on the Apply-button, the date reformats to US (mm-dd-yyyy).
Example:
When choosing 5th of September 2007 and clicking apply, it shows in the picker, 9th of May 2007.
When choosing 26th of September 2007 and clicking apply, it shows, again in US format, the RIGHT date but adds a timestamp 12:00 AM? in the end, making further enquiries to fail.
The report itself receives the right date and shows correctly. The only case it fails is, when the time stamp appears.
The server is a 32-bit one with 4 GB RAM. A testserver with identical collation on the Reportserver database cannot recreate the error. The site containing the reports has been set to Danish in the regional settings. To Reinstall is not an option.
The test report has no database connection whatsoever.
When setting the site to US, the timestamp wont appear at all.
The server has been restarted and the installation procedure was of the simple kind. No special tweaks at all.
Any advice would be greatly appreciated.
Kind Regards
Johan Rastenberger
View 1 Replies
View Related
Nov 26, 2015
CREATE TABLE #Dateissue
(ID int,
Code nvarchar (20),
Datein datetime,
Declined datetime )
[Code] .....
I have a table here. I want find a way of getting the latest date, when the code is the same. If the Declined date is null. Then I still want the latest date. E.g. ID 3.
If the declined date is filled in. Then I want to get the row, when the Datein column value is greater then the declined date only.
I tried grouping it by max date, but i got an error message when trying this out. Against the code
WHERE MAX(Datein) > Declined
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference. What do I need to do to get both my outputs working?
View 9 Replies
View Related
Oct 28, 2015
i have written a sql function which returns only number of working days (excludes holidays and Weekends) between given StartDate and EndDate.
USE [XXX]
GO
/****** Object: UserDefinedFunction [dbo].[CalculateNumberOFWorkDays] Script Date: 10/28/2015 10:20:25 AM ******/
SET ANSI_NULLS ON
GO
[code]...
I need a function or stored procedure which will return the date which is 15 working days (should exclude holidays and Weekends) prior to the given future Date? the future date should be passed as a parameter to this function or stored procedure to return the date. Example scenario: If i give date as 12/01/2015, my function or stored procedure should return the date which is 15 working days (should exclude holidays and Weekends) prior to the given date i.e 12/01/2015...In my application i have a table tblMasHolidayList where all the 2015 year holidays dates and info are stored.
View 18 Replies
View Related
Jul 6, 2015
A date field in a report returns the date value as:
2015-07-01 13:30:27.000
Is there any way I can script this to appear as:
01 July 2015 (or 01-07-2015 or 01/07/2015) - basically to cut out the hours, minutes and seconds?
The best I have managed is: CONVERT (varchar(17),DATE,113) AS Date1 but this still leaves me with:
01 July 2015 13:30
View 9 Replies
View Related
Oct 7, 2015
How do you Write an expression to Return the date value based on the most recent date only?
Eg. Date 10-7-2015, action - done, notes - all items fixed.
Date 4-5-2016, action - not yet done, notes - buying parts
All these dates are returned based on a search parameter based on a project number. I only want the most current date and associated fields displayed.
View 4 Replies
View Related
Jul 24, 2007
I am having report parameter end date I am showing the default value "5/21/2007 11:59:59 PM" in the end date paramter. And also I want to show date time format "(MM/DD/YYYY HH:MMS)" in the right hand side of the parameter. How to do this?
Thank You
View 1 Replies
View Related
Aug 31, 2015
So my data column [EODPosting].[MatchDate] is defined as a DATE column. I am trying to SELECT from my table where [EODPosting].[MatchDate] is today's date.
SELECT*
FROM[dbo].[EODPosting]
WHERE[EODPosting].[MatchDate]=GETDATE()
Is this not working because GETDATE() is like a timestamp format? How can I get this to work to return those rows where [EODPosting].[MatchDate] is equal to today's date?
View 2 Replies
View Related
May 4, 2007
Hi,
I have soma ado.net code that inserts 7 parameters in a database ( a date, 6 integers).
I also use a self incrementing ID but the date is set as primary key because for each series of 6 numbers of a certain date there may only be 1 entry. Moreover only 1 entry of 6 integers is possible for 2 days of the week, (tue and fr).
I manage to insert a row of data in the database, where the date is set as smalldatetime and displays as follows: 1/05/2007 0:00:00 in the table.
I want to retrieve the series of numbers for a certain date that has been entered (without taking in account the hours and seconds).
A where clause seems to be needed but I don’t know the syntax or don’t find the right function
I use the following code to insert the row :
command.Parameters.Add(new SqlParameter("@Date", SqlDbType.DateTime, 40, "LDate"));
command.Parameters[6].Value = DateTime.Today.ToString();
command.ExecuteNonQuery();
and the following code to get the row back (to put in arraylist):
“SELECT C1, C2, C3, C4, C5, C6 FROM Series WHERE (LDate = Today())?
WHERE LDate = '" + DateTime.Today.ToString() + "'"
Which is the correct syntax? Is there a better way to insert and select based on the date?
I don’t get any error messages and the code executes fine but I only get an empty datatable in my dataset (the table isn’t looped for rows I noticed while debugging).
Today’s date is in the database but isn’t found by my tsql code I think.
Any help would be greatly appreciated!
Grtz
Pascal
View 5 Replies
View Related
Mar 18, 2014
I have a query to run a report where the results has a column named “Due Date” which holds a date value based on the project submission date.Now, I need to add 4 columns named, “45 Days Expectant”, “30 Days Overdue”, “60 Days Overdue” and “90 Days Overdue”.I need to do a calculation based on the “Due Date” and “System (I mean default computer date) Date” that if “System Date” is 45 days+ to “Due Date” than put “Yes” in “45 Days Expectant” row.
Also, if “Due Date” is less than or equal to system date by 30 days, put “Yes” in “30 Days Overdue” and same for the 60 and 90 days.how to write this Case Statement? I have some answers how to do it in SSRS (Report Designer) but I want to get the results using T-SQl.
View 2 Replies
View Related
