VS05
How are dates removed / nullified?
I have a SQL datetime field that is being editted via a datatable / adapter - The table structure defines the datetime as a date.
From here I want to remove the date - i.e. write DBNull back to the database. Setting the Date to Nothing or .minvalue results in a min date exception and DBNull cannot be cast to the datetime either.
R
Hi, I have a database field for completion dates - until a task is completed, there is no date and at the moment I have null values in this field.My problem arises when searching the records. I have a search form which passes parameters via a query string to a SqlDataSource. The SqlDataSource has the CancelSelectOnNullParameter set to true, so if any fields on the search form are left blank, they are ignored. For other dates, my sql query contains something like (Job.EnteredAt < ISNULL(DATEADD(d, 1, @EntTo), '2099-01-01')) This approach doesn't return any records for the completion date as there is no date to compare to the '2099-01-01'.Can anyone give me any tips on how I should handle this? I'm willing to change my structure, search page or sql query!Thanks, Neil
View 1 Replies View RelatedMy users want to be able to enter nothing in a date field.
I'm using asp.net v2, vb.net, and VS 2005 for my application. I'm not sure what to do or what code to write to allow the user not to enter a date and keep from hitting the sqldatetime overflow error.
I could use some help.
Thanks
Hi EveryoneVery new to .net and currently dipping my toes in the water with a small application, but getting to the point -
I have a form which has somel text fields that expect a date but which are not a required field so in other words the user can leave them blank.
The code behind page stores the information using a stored procedure which I add parameters to in the following fashion -
SqlParameter userdate = new SqlParameter();userdate .ParameterName = "@dtdate";userdate .SqlDbType = SqlDbType.DateTime;userdate .Direction = ParameterDirection.Input;userdate .Value = dtdate.Text.ToString();cmd.Parameters.Add(userdate);
Now if I leave the text field dtdate blank I receive an error because the above expects a date.
If I remove the line userdate .SqlDbType = SqlDbType.DateTime; I don't recieve an error but my stored procedure saves the date as 01/01/1900 or similar.
I believe this is because in my stored procedure the paramger dtdate is defined as @dtdate datetime
Obviously I want to have it so that if the user leaves the text field empty then no date is saved in the database and was wondering how other people tackle this scenario.
Re: SQL Server Reporting Services
I have welders who have active dates and inactive dates, and I need to create an "isactive" column in the report. My formula will be pretty obvious to most of you. However, if the user does not input an inactive date, that means none has been given and therefore the welder is currently active. The problem is that there seems to be no way to check if inactive date is null or not in the formula.
Please help! Thanks!
I have been looking for the answer to this for a while, but probably haven't found the right place or query. I want to achieve the following:Table 1Table 2
DateShiftDateShiftData
---------------------------------------------------------------------------------
1/1/200711/1/20072Some data
1/1/200721/2/20073Some more data
1/1/20073
1/2/20071
1/1/20072
1/1/20073
and generate:
Table 3
DateShiftData
--------------------------------------------------------
1/1/20071NULL
1/1/20072Some data
1/1/20073NULL
1/2/20071NULL
1/1/20072NULL
1/1/20073Some more data
This way, the information can be displayed and show that some of the entries were not entered for the dates with NULL. Thanks for the help, -Syn
Hi,
I have a Excel file as data source. On sheet 1 there are some header information. On sheet 2 are a quiet simple data table. Both sheets will be read into memory with SSIS.
Now i want to combine some header information with the data table.
Sheet 1:
Unit A, 3/20/2008, Group 1
Sheet 2:
34‚¬, 56‚¬, 12‚¬
43‚¬, 43‚¬, 12‚¬
32‚¬, 42‚¬, 13‚¬
...
This should be result:
34‚¬, 56‚¬, 12‚¬, Unit A, 3/20/2008, Group1
43‚¬, 43‚¬, 12‚¬, Unit A, 3/20/2008, Group1
32‚¬, 42‚¬, 13‚¬, Unit A, 3/20/2008, Group1
...
How can I do this?
Regards
Johannes.W
I am still confounded with how to create a VIEW involving multiple DATATABLES in a DATASET. Could someone point out a MSDN page where I can see this working and how I go about doing this? Let me re-iterate - I want to create a VIEW that uses JOIN type statements that involves multiple DATATABLES in my loaded DATASET.
View 1 Replies View RelatedI dragged two tables into the dataset designer. And I have a query with a resultset over both of the tables (joined).Is there ANY way I can have a strongly typed datatable out of the resultset?The automatically created dataset adapters won't allow me to create a tableadapter or datatable for the joined resultset but for one single table at a time only.Any solution to that? Theoretically it should be possible since I don't "lose" any type information with a join.. why can't I have a strongly typed datatable over the resultset?The join is not very sophisticated. In fact it couldn't be any more simple.(I'm refering to my previously posted question at http://forums.asp.net/t/1220481.aspx)
View 4 Replies View RelatedGood Day:
I have a business object with a set of public DataTables and Public Strongly Typed Dataset DataTable Types defined. When I add the business object class to the report as a datasource, only public System datatypes (int, string) show up. I added System.Data to the report properties Assemblies, with no change. Any ideas?
Thanks,
Omar
Hi, I am new to Crystal Reports. I have a dataset with three datatables that are related. How can I do a subquery on these tables and display its result on my Crystal report ?
View 1 Replies View RelatedThe test sub below operates on a SQL Server Table with an xml-type field ("xml"). The purpose of the sub is to learn about storing and retrieving a whole xml document as a single field in a SQL Server table row.When the code saves to the xml field, it somehow automagically strips the xml.document.declaration (<?xml...>). So when it reads the xml field back and tries to create an xmldocument from it, it halts at the xmldocument.load.I order to get the save/retrieve from the xmlfield to work, I add the <?xml declaration to the string when I read it back in from the xml field (this is in the code below).At that point the quickwatch on the string I'm attempting to load into the xmldocument is this:-----------------------------------------------------<?xml version="1.0" encoding="utf-16" ?><Control type="TypeA"><Value1><SubVal1A>Units</SubVal1A><SubVal1Btype="TypeA">Type</SubVal1B></Value1><Value2><SubVal2A>Over</SubVal2A><SubVal2B>Load</SubVal2B></Value2></Control>-----------------------------------------------------The original xml document string is this:-----------------------------------------------------<?xml version="1.0" encoding="utf-16" ?><Control type="TypeA"> <Value1> <SubVal1A>Units</SubVal1A> <SubVal1B type="TypeA">Type</SubVal1B> </Value1> <Value2> <SubVal2A>Over</SubVal2A> <SubVal2B>Load</SubVal2B> </Value2></Control>-----------------------------------------------------which seems to have all the same characters as the quickwatch result above, but clearly is formatted differently because of the indenting.THE FIRST QUESTION: Is there a simpler way to do this whole thing using more appropriate methods that don't require adding the xml.document.declaration back in after reading the .xml field, or don't require using the memorystream to convert the .xml field in order to load it back to the XML document.THE SECOND QUESTION: Why does the original document open in the browser with "utf-16", but when I write the second document back to disk with "utf-16" it won't open...I have to change it to "utf-8" to open the second document in the browser.Here's the test sub'============================================ Public Sub XMLDSTest() '=========================================== Dim ColumnType As String = "XML" '=========================================== '----------Set up dataset, datatable, xmldocument Dim wrkDS As New DSet1() Dim wrkTable As New DSet1.Table1DataTable Dim wrkAdapter As New DSet1TableAdapters.Table1TableAdapter Dim wrkXDoc As New XmlDocument wrkXDoc.Load(SitePath & "App_XML" & "XMLFile.xml") Dim str1 = wrkXDoc.OuterXml Dim wrkRow As DSet1.Table1Row wrkRow = wrkTable.NewRow '=======WRITE to SQL Server============== '------ build new row With wrkRow Dim wrkG As Guid = System.Guid.NewGuid TestKey = wrkG.ToString .RecordKey = TestKey .xml = wrkXDoc.OuterXml '<<< maps to SQL Server xml-type field End With '----- add row to table and update to disk wrkTable.Rows.Add(wrkRow) wrkAdapter.Update(wrkTable) wrkTable.AcceptChanges() '----- clear table wrkTable.Clear() '=======READ From SQL Server ============== '----refill table, read row, wrkAdapter.FillBy(wrkTable, TestKey) Dim wrkRow2 As DSet1.Table1Row = _ wrkTable.Select("RecordKey = '" & TestKey & "'")(0) '===== WRITE TO New .xml FILE =========================== Dim wrkS1 As New StringBuilder Select Case ColumnType Case "XML" '---if xml build xml declaration: '---add this to xml from sql table => <?xml version="1.0" encoding="utf-16" ?> wrkS1.Append("<?xml version=" & Chr(34) & "1.0" & Chr(34)) wrkS1.Append(" encoding=" & Chr(34) & "utf-16" & Chr(34) & " ?>") wrkS1.Append(wrkRow2.xml) End Select Dim wrkBytes As Byte() = (New UnicodeEncoding).GetBytes(wrkS1.ToString) Dim wrkXDoc2 As New XmlDocument Dim wrkStream As New MemoryStream(wrkBytes) wrkXDoc2.Load(wrkStream) '=========================================== '---- this just shows that the file actually was touched Dim wrkN2 As XmlNode = wrkXDoc2.CreateNode(XmlNodeType.Text, "ss", "TestNode2") wrkN2 = wrkXDoc2.SelectSingleNode("//Value1/SubVal1B") wrkN2.Attributes("type").Value = "This was from the xml field" '---------------- '------ update the encoding....otherwise the file won't open in the browser with utf-16 Dim wrkN1 As XmlNode = wrkXDoc2.CreateNode(XmlNodeType.Element, "ss", "TestNode") wrkN1 = wrkXDoc2.FirstChild wrkN1.InnerText = Replace(wrkN1.InnerText, "utf-16", "utf-8") '------------Now write the file back as an .xml file Dim wrkFilePath As String = SitePath & "App_XML" & "XMLFile2.xml" Dim wrkXW As XmlWriter = XmlWriter.Create(wrkFilePath) wrkXDoc2.WriteContentTo(wrkXW) wrkXW.Close() End Sub===============================
View 8 Replies View RelatedI'm writing a Windows application (Visual Studio 2005, c#) utilizing a local SQLExpress database. It consists of about 10 tables and I've created about 15 Stored Procedures to address various functions... I can run the update Stored Procedures interactively within the designer and the data tables update as designed. However, if I run the update Stored Procedures from within my windows application is where they fail. I get no error messages, if I return a rowcount variable from the Stored Procedure it tells me that one row was updated (SELECT @RtnVal == @@rowcount)... but when I open the subject table in the designer, there is no new data.
My update queries address both insert and update functions, so if it is new row of data, it performs the insert action, otherwise it updates an existing row.
I can query the data using my stored procedures to load default values into my windows form, I can search and find client records to display in the form... I just can't update records. I'm of the opinion that it is a rights issue, but I can't find any resources that address user access accounts with SQLExpress and windows apps. The current connection string for my local database is set for Integrated Security = true; User Instance = true
I've tried two approaches... one utilizes my stored procedure....
bool bSave;
SqlConnection conn = new SqlConnection(KadaDesk.Properties.Settings.Default.dbKadaConnectionString.ToString());
SqlCommand cmd = new SqlCommand("SavAuthTesterData", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@CtrId", SqlDbType.NVarChar, 20).Value = TestCenterID.ToString();
cmd.Parameters.Add("@AT_Id", SqlDbType.NVarChar, 15).Value = GnuAuthTesterId.ToString();
cmd.Parameters.Add("@AT_Name", SqlDbType.NVarChar, 50).Value = GnuAuthTesterName.ToString();
cmd.Parameters.Add("@AT_Pwd", SqlDbType.NVarChar, 15).Value = GnuAuthTesterPwd.ToString();
cmd.Parameters.Add("@Maint", SqlDbType.NChar, 1).Value = GnuAuthTesterStatus.ToString();
cmd.Parameters.Add("@ModBy", SqlDbType.NVarChar, 15).Value = sAuthTester.ToString();
try
{
conn.Open();
cmd.ExecuteNonQuery();
bSave = true;
}
Then I tried a direct insert...
SqlConnection conn = new SqlConnection(KadaDesk.Properties.Settings.Default.dbKadaConnectionString.ToString());
SqlCommand cmd = new SqlCommand();
SqlTransaction myTrans;
conn.Open();
cmd.Connection = conn;
myTrans = conn.BeginTransaction();
cmd.Transaction = myTrans;
string disDte = DateTime.Now.ToShortDateString();
try
{
string sCmdText = "INSERT INTO Tester (CenterId, AuthTesterId, AuthTesterName, AuthTesterPwd, "
+ "CreateDte, Maint, ModBy) "
+ "VALUES ('" + TestCenterID.ToString() + "','"
+ GnuAuthTesterId.ToString() + "','"
+ GnuAuthTesterName.ToString() + "','"
+ GnuAuthTesterPwd.ToString() + "','"
+ disDte.ToString() + "','"
+ GnuAuthTesterStatus.ToString() + "','"
+ sAuthTester.ToString() + "')";
cmd.CommandText = sCmdText;
cmd.ExecuteNonQuery();
myTrans.Commit();
bSave = true;
}
Both fail... which points to the only common point in both approaches, the connection string and user rights... but I can't find any place to address user accounts or configurations for windows apps.
Thanks for whatever help you can offer...
Jim-
Hey. I need to substitute a value from a table if the input var is null. This is fine if the value coming from table is not null. But, it the table value is also null, it doesn't work. The problem I'm getting is in the isnull line which is in Dark green color because @inFileVersion is set to null explicitly and when the isnull function evaluates, value returned from DR.FileVersion is also null which is correct. I want the null=null to return true which is why i set ansi_nulls off. But it doesn't return anything. And the select statement should return something but in my case it returns null. If I comment the isnull statements in the where clause, everything works fine. Please tell me what am I doing wrong. Is it possible to do this without setting the ansi_nulls to off??? Thank you
set ansi_nulls off
go
declare
@inFileName VARCHAR (100),
@inFileSize INT,
@Id int,
@inlanguageid INT,
@inFileVersion VARCHAR (100),
@ExeState int
set @inFileName = 'A0006337.EXE'
set @inFileSize = 28796
set @Id= 1
set @inlanguageid =null
set @inFileVersion =NULL
set @ExeState =0
select Dr.StateID from table1 dR
where
DR.[FileName] = @inFileName
AND DR.FileSize =@inFileSize
AND DR.FileVersion = isnull(@inFileVersion,DR.FileVersion)
AND DR.languageid = isnull(@inlanguageid,null)
AND DR.[ID]= @ID
)
go
set ansi_nulls on
SQL 2005 Dev
How can I do this with Parameters? I can get a single parameter to filter for a single date (or even a combo list of the dates in DB). But I want my parameters to interact so that they specify a range. Is this possible?
Today I have got one scenario to calculate the (sum of days difference minus(-) the dates if the same date is appearing both in assgn_dtm and complet_dtm)/* Here goes the table schema and sample data */
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_tbl]') AND type in (N'U'))
DROP TABLE [dbo].[temp_tbl]
GO
CREATE TABLE [dbo].[temp_tbl](
[tbl_id] [bigint] NULL,
[cs_id] [int] NOT NULL,
[USERID] [int] NOT NULL,
[code]....
I have a table that has hotel guests and their start stay date and end stay date, i would like to insert into a new table the original information + add all days in between.
CREATE TABLE hotel_guests
(
[guest_name] [varchar](25) NULL,
[start_date] [date] NULL,
[end_date] [date] NULL,
[comment] [varchar](255) NULL
[code]...
I want to know if there is a way to compare dates in the sql statement with dates that I input into a database and todays date. the datatype that I'm using is smalldatetime.The statement I used is:Select Date from Table where Date > 'Today.now'I get an errorCould this be done or is there another approach?
View 1 Replies View RelatedIn my report i have CNAME parameter , which allows null value. I checked Allow null value check box in report parameter properties.
when i preview the report , it displays checked NULL check box beside CNAME parameter . I want to give some meaningful name(i.e.ALLCustomers) to this checkbox instead of NULL.Â
Is it possible through SSRS designer?
I have a flat file with the following columns
SampleID  Rep_Number  Product  Protein  Fat  Solids
In the flat file SampleID and Product are populated in the first row only, rest of the rows only have values for Rep_Number, Protein, Fat, Solids.
SampleID and Product are blank for the rest of the rows. So my task is to fill those blank rows with the first row that has the sampleID and Product and load into the table.
I have a report that is run on a monthly basis with a default date of null. The stored procedure determines the month-end date that it should use should it be sent a null date.
The report works fine when I tell it to create a history entry; however, when I try to add a subscription it doesn't appear to like the null parameter value. Since I have told the report to have a default value of null it doesn't allow me to enter a value on the subscription page.
Now, I suppose I could remove the parameter altogether from the stored proc, but then the users would never be able to run the report for a previous time period. Can someone explain to me why default values aren't allowed to be used on subscriptions when they seem to work fine for ad hoc and scheduled reports? This is really quite frustrating as most of my reports require a date value and default to null so that the user doesn't have to enter them for the latest data.
An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help
Key cannot be null. Parameter name: key
I run a stored procedure for which I have a return variable. The stored procedure returns the ID of a row in a table if it exists:
m_sqlCmd.ExecuteScalar();
The m_sqlCmd has been fed an SQLParameter with direction set to output.
When the stored proc returns, I want to test it. Now when there IS a row it returns the ID ok.
When the row doesn't exist, in my watch I have:
m_sqlParam.SqlValue with value {Null}
I can't seem to work out how to test this value out.
I've tried several things but none seem to work.
This line compiles ok, but the following runs into the IF statement as if the SqlValue is null??
if (m_sqlParam.SqlValue != null)....
{
// I'm here!! I thought the watch says this is null???
}
Sorry if this is obvious, but I can't work this one out!!
Looks like there was a fix and then I read this fix is not a fix. Does anyone know how this can be rectified? Does it mean that only Windows authentiation is the only way it works. The Software is over 2 years old, there are no excuses.
Hi,
I have a table which records employees'time-off records. There are 6 columns in this TimeOff table. They are RequestID, EmpName, StartDate, EndDate, Type, NumofDays. I have another table which has all the dates from 01/01/1950 to 01/01/2056.
I am trying write a query which lists all the dates between the timeoff startdate and enddate, including the the start and end dates, but my query so far only lists the start and end date in a timeoff record:
SELECT D.[Date], Datename(dw,D.[Date]) AS Weekday
FROM Dates D LEFT JOIN TimeOff T ON D.[Date] = T.OffStartDate OR D.[Date] = T.OffEndDate
WHERE (OffType = 'Sick Day' AND EmpName = 'Cat White') AND (D.[Date] BETWEEN T.StartDate AND T.EndDate)
Any advice will be greatly appreciated!
Has anyone ever written a function to retrieve all individual dates between two given dates? Using DATEDIFF I can get the number of days between two dates. However I need to iterate through the days to identify weekend and holiday dates. Has anyone ever written a function to do this?
So, if select datediff(d,'07/01/2007','07/15/2007') as NumOfDays returns 14, I'd need to iterate through the 14 days and get the weekends and holidays. Would I have to use a cursor to iterate through the days?
I am getting this error: "Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails." -- But my value is not null. I did a response.write on it and it show the value. Of course, it would be nice if I could do a breakpoint but that doesn't seem to be working. I'll attach a couple of images below of my code, the error, and the breakpoint error.
Server Error in '/' Application.
Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.Source Error:
Line 89: sContact.Phone = sPhone.Text.Trim
Line 90: sContact.Email = sEmail.Text.Trim
Line 91: sContact.Save()
Line 92:
Line 93: Dim bContact As Contact = New Contact()Source File: F:InetpubwwwrootOutman KnifeCheckout.aspx.vb Line: 91 Stack Trace:
[SqlException (0x80131904): Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857354
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734966
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlDataReader.HasMoreRows() +150
System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) +214
System.Data.SqlClient.SqlDataReader.Read() +9
System.Data.SqlClient.SqlCommand.CompleteExecuteScalar(SqlDataReader ds, Boolean returnSqlValue) +39
System.Data.SqlClient.SqlCommand.ExecuteScalar() +148
SubSonic.SqlDataProvider.ExecuteScalar(QueryCommand qry) +209
SubSonic.DataService.ExecuteScalar(QueryCommand cmd) +37
SubSonic.ActiveRecord`1.Save(String userName) +120
SubSonic.ActiveRecord`1.Save() +31
Checkout.btnCheckout_Click(Object sender, EventArgs e) in F:InetpubwwwrootOutman KnifeCheckout.aspx.vb:91
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42
I would drop and add the table but the data can't be deleted. So if anyone could help with the statement it would be greatly appreciated. Thanks
View 7 Replies View RelatedI'm trying to set the concat... option to OFF
all the time and in all my databases
I tried this command
USE master
EXEC sp_dboption 'DatabaseName', 'concat null yields null', 'FALSE'
but it doesn't change anything
Select NULL + 'TOTO'
----> NULL
(it should be 'TOTO')
hi,my structure table in database:Amount float(53) not null default 0when i try to run his script:alter table ABC alter column Amount float(53) nullit can only set the Amount to allow null, but can't set the defaultvalue to empty.anyone know how to set the field to allow null and default set toempty, no value.thanks
View 5 Replies View RelatedIt's giving me an error while I'm trying to change column value from not null to null..
'tblid' table
- Unable to modify table. Â
Cannot insert the value NULL into column 'ValidID', table 'Xe01.dbo.Tmp_tblid'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Hello.
I've built a sample CLR function with the following declaration....
CREATE FUNCTION GetManager(@DeptCode nvarchar(3))
RETURNS nvarchar(1000)
WITH RETURNS NULL ON NULL INPUT
AS
EXTERNAL NAME Assembly1.[ClassLibrary1.MyVBClass].MyManager
And it works as expected, except when I use NULL:
DECLARE @MyManager nvarchar(1000)
EXEC @MyManager = dbo.GetManager NULL
PRINT @MyManager
It returns the value "Unknown" as it would have for any unknown DeptCode, as-programmed.
I'm of the theory it should have returned NULL without actually firing the function? Or is this only for non-CLR items... or stored procedures, not functions?
We have a case where in we should show date based on conditions for e.g if we had a column defined as
col varchar(10) then we would show col as 'NULL' for some condition and actual value when no condition
Normaly date values are stored here e.g under col 20150901 .
CaseÂ
when col>'20150901' then 'NULL'
else col
end as Derivedcol
Note this is an extract process and we are presenting data by pumping the data in a table .
Now there is another similar column -colz varchar(10) Â which stores date but doesnt have case condition so whenever date has no value its shows null which is database null.
So whats the difference between database null and string null ?
 How can we show database null for the case condition instead of string "null"?
I'm trying to generate this query, that displays Budget Current Year , Actual Current Year and Prior Year Revenue. When It comes to the Budget and Actual everything works fine, however when I try to add the query for the Prior Year I get an error, and I realized that the leap date is causing the error
Here is what I'm trying to generate
InnCodeID
Quarterly
Monthly
Days
Period
Year
BARmRev
AARmRev
PYRmRev
ADDIS
Q1
Jan
1
1
2008
NULL
NULL
ADDIS
Q1
Jan
1
1
2008
3462.14
5107.65
ADDIS
Q1
Jan
1
1
2008
NULL
NULL
ADDIS
Q1
Jan
1
1
2008
NULL
NULL
Here is the error that I'm getting:
Code Snippet
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
(4834 row(s) affected)
Here is my Transact-SQL Syntax (summarized because I Couldn't post it):
SELECT
(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01'
AND Year(dbo.Trans.TR_Date) = Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1'
THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN
(SELECT Trans1.TR_Amount
FROM dbo.Trans Trans1
WHERE Trans1.TR_Dept = '10' AND TR_Main = '5120' AND TR_Sub = '01' AND trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date)
AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Year(dbo.CurrentDate.CurrDate) AS varchar(4))) AS datetime)
AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND trans1.datatype = dbo.Trans.DataType) ELSE NULL END) * - 1 AS BARmRev,
--AA Script Here AS AARmRev,
(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01' AND Year(dbo.Trans.TR_Date)
= Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1' THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN
(SELECT SUM(Trans1.TR_Amount)
FROM dbo.Trans Trans1
WHERE RIGHT(RTRIM(Trans1.TR_Dept), 2) = '10' AND Trans1.TR_Main = '5120' AND Trans1.TR_Sub NOT BETWEEN '04' AND '05' AND
trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2))
+ '/' + CAST(Year(dbo.CurrentDate.CurrDate)-1 AS varchar(4))) AS datetime) AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND
trans1.datatype = '0') ELSE NULL END) * - 1 AS PYRmRev
FROM dbo.EntityDef INNER JOIN
dbo.Trans ON dbo.EntityDef.ED_Property_ID = dbo.Trans.TR_Entity INNER JOIN
dbo.CurrentDate INNER JOIN
dbo.DimTime ON YEAR(dbo.DimTime.TimeDate) = YEAR(dbo.CurrentDate.CurrDate) ON dbo.Trans.TR_Date = dbo.DimTime.TimeDate
WHERE (dbo.EntityDef.ED_Property_ID = 'ADDIS')
ORDER BY dbo.EntityDef.ED_Property_ID, dbo.DimTime.TimeDate
I appreciate all your help
Thanks