.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Easy Web
Imran Ghani
Post New Web Links

how to load dates in a table in a range

Posted By:      Posted Date: September 10, 2010    Points: 0   Category :Sql Server
 
Hi i have a table DATEDESC with columns [FullDate] [DateName] [YearName] [MonthName] [DayOfTheWeek] [YearNumber] [MonthNumber] [WeekNumber] [DayNumber] SAMPLE DATA OUTPUT FULL_DATE DATE YEARNAME MONTH_NAME DAY_NAME YEAR MONTH_NUMBER WEEK_WITHIN_THE_MONTH WEEK_WITHIN_THE_YEAR DAY_NUMBER 2010-09-09 24:31.9 2010-09-09 NOT LEAP YEAR September Thursday 2010 9 2 37 9   I need to load a series of dates into this tale by giving a start and and end dates range. Lets say i need to load 10yrs of date's data into this column, i need to load all the dates into this table and get those respective column. The query i had to populate the sample data was with the current date. But i am not sure how to do this for a bunch of data when it is in a range. SELECT GETDATE() AS FULL_DATE, Convert(varchar(10), getdate(),110) as DATE_NAME, CASE (YEAR(GETDATE()))%4 WHEN 0 THEN 'LEAP YEAR'ELSE 'NOT LEAP YEAR' END AS [YEAR_NAME], DATENAME(MONTH,GETDATE()) AS [MONTH_NAME], DATENAME("DW",GETDATE()) AS [DAY_OF_THE_WEEK], DATENAME(YEAR,GETDATE()) AS [YEAR_NUMBER], MONTH(GETDATE()) AS MONTH_NUMBER, DATEPART(DAY,GETDATE() -1)/7+1 AS [WEEK_WITHIN_MONTH], DATENAME("WEEK",GETDATE()) AS [WEEK_WITHIN_YEAR], DAY(GETDATE()) AS  DAY_NUMBER So, could you please help me in build this query. Thank you.


View Complete Post


More Related Resource Links

expand table with date range for each dates

  

Hi, 

 

         I have a scenario to expand the resultant table with dates column,.

 

For ex:

select patientid, patientname, servicefrom, serviceto from appointment

will result the following table

 

patientid      patientname      servicefrom      serviceto

  a1                alex               02/05/2010       02/08/2010

  a2                brad              02/09/2010        02/09/2010

  a1                alex               02/07/2010       02/10/2010

 

i want to write a query to expand the tables like

 

patientid      patientname      servicefrom      serviceto           eachdates

  a1                alex               02/05/2010       02/08/2010      02/05/2010

 

How to use OPENXML to load XML data into existing SQL Table?

  
I am a newbie to OPENXML. But I am trying to load a .XML file into a SQL table that I created for this. I do not receive any errors with this code, but it doesn't insert any records either. This is the table I created in 2008 SQL Server: CREATE TABLE HOMEROOM( HOMEROOM_TEACHER INT, HOMEROOM_NUMBER INT, ENTITY_ID INT) And this is the T-SQL code I am trying to execute: DECLARE @idoc int DECLARE @xmlDocument varchar(MAX) DECLARE @Status INT SET @xmlDocument =' <xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema"> <s:Schema id="RowsetSchema"> <s:ElementType name="row" content="eltOnly"> <s:AttributeType name="c0" rs:name="HOMEROOM-TEACHER" rs:number="1" rs:nullable="true"> <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true" /> </s:AttributeType> <s:AttributeType name="c1" rs:name="HOMEROOM-NUMBER" rs:number="2"> <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="10" rs:maybenull="false" /> </s:AttributeType> <s:Attri

My repeating table did not open/load correctly

  
In my browser-enabled form I am using a webservice to get data from one look up table in my database. The table consists of two columns (ProdId and ProdDescription). In order to allow the user to select more than one products, I bind  this data to a repeating table where the productId column converted from a check box to a checkbox instead. So every row of this repeating table have a checkbox in the first column so the user can make selection and a textbox in the second column to list the Product Description. When the user open/load the existing form where multiple selection was made to the repeating table none of the checkboxs were checked. I have no idea how to go about to fix this issue and i can use some help. So far i have not use any codes for my form so i would like to stay codeless and using rules instead. Thank you in advance.

How to load a new table with the value of a variable from SSIS package?

  
Hi, i have two variables in a SSIS package Var1 and Var2. Both of these variables have values. is there any way i can put the values of these two variables in a new table? e.g In New table col1 having value of Var1 and col2 having value of Var2. Thanks

Capture the xml filename and load in a table

  
Hey I am having folder which contains multiple xml files.Now I need to read the xml file name and write in a table. I follwed the steps which are in the following link. http://www.sqlservercentral.com/articles/SSIS/67871/ But I am getting data as well as. I need only the file name to be written in a table.

Table Adapter generates bad SQL under load

  
I have an application (ASP.NET 2.0/SQL Server 2005) which makes heavy use of table adapters for pulling records from SQL. Under heavy load, we get a lot of SQL Server Timeout errors. We have run a trace on SQL Server and it shows that several of the SQL statements being passed into SQL Server, from the Table Adapters, have bad SQL. For example, here is the SQL in one of the table adapters SELECT    HomeMsgID, messageName, messageHTML, messageText, populationID FROM        MyUCR_HomeMessages WHERE    (populationID IN                           (SELECT    populationID                             FROM          MyUCR_Population_CPID AS MyUCR_Population_CPID_1                             WHERE      (CPID = @CPID))) AND (isVisible = 1) AND (showDate <= @showDate) AND (removeDate >= @removeDate) I call it with the following: DateTime showDate = DateTime.Today; DateTime removeDate = DateTime.Today; myUCR_HomePageMsgsTableAdapters.MyUCR_HomeMessagesTableAdapter ta = new myUCR_HomePageMsgsTableAdapters.MyUCR_HomeMessagesTableAdapter(); myUCR_HomePageMsgs.MyUCR_HomeMessagesDataTable dt = new myUCR_HomePageMsgs.MyUCR_HomeMessagesDataTable(); ta.FillByCPID(dt, showDate, removeDate, CPID); What the SQL trace shows, when it fails, is this (notice the extra single quotes around the showDate, removeDate parameters): E000 exec sp_executesql N'SELECT    HomeMsgID, messageN

trouble getting dates right when importing Access table

  
I was given a bunch of data in Access and am putting it in SQL 2005 for an ASP.NET web app. Tryingto do a query to select only records where the current date is after the "postDate" field and on or before the "expireDate" field. WHERE (postDate <= CURRENT_TIMESTAMP) AND (expireDate >= CURRENT_TIMESTAMP) I'm getting no records returned even though there are some records that qualify. Tried using CURDATE() and NOW(), no difference. At this point I think it may be because the dates are not in the correct format. The columns were originally "Date/Time" format in Access and when I imported them into SQL using the DTSWizard, the datatype for those columns became "smalldatetime." Dates are in the format M/DD/YYYY HH:MM:SS. I'm guessing the query won't work because SQL is expecting YYYY-MM-DD. How do I fix this? Been spending a depressing amount of time running in circles with this.

Help in Building Fiscal Dates Table

  
hi Experts,  I'm trying to build fiscal periods table. I have Calendar Dates of year 2007  in 1 table and just start Dates of Fiscal weeks in another table, can any one suggest me about building Fiscal table containing entire year using the 2 tables.  thank you ....   

Variable value within SQL Server table Configuration is not being updated within load of DTSX packag

  

Hi,

I am probably missing something obvious here; -
I have created a package with a variable named; - ServerName and a value of 'KIERAN-PC'
I exported this variable as an SSIS configuration within a SQL Server table

I noticed the following record appeared within the SQL Server configuration table.

 

Creating Table of consecutive dates

  

I'm trying to create a table with one field (SmallDate&Time)

I want this table to have consecutive dates beginning roughly 01/01/2008

through 12/31/2013.

I think I need a Stored procedure to do this but am not sure.

It may even be doable from an SQL Query.

Any help would be appreciated.

 

Regards

 

Tattoo

 

 

 

 


query wich returns records for each day between start date from table 1 and mutation dates in table

  

Hello,

I have to tables with dates, table Employments with all employements of all employees, with start date and end date, and a table Mutations with mutations (e.g in salary) with start dates.

Now I try to write a query which returns a record for each day an employee is in employment, with the correct salary. SO at first, it should return all days between the employment start date and the first mutation date, then the number of days between the first mutation date and the second mutation date etcetc, and at last the number of days between the last mutation date and the employment end date. The number of mutations varies for each employment, and employees cna have multiple employments (history, so not at the same time) in the employments table.

How to do this?


List ALL Dates in Date Range When Selecting Count

  

Say I have a table called "Visits", and I want to create a query that counts the visits per day. 

The way I would accomplish this would be to create a query as such:

SELECT Count(VisitId) as VisitCount, Convert(varchar(32), VisitDateTime, 101) as VisitDate
FROM Visits
WHERE VisitDateTime BETWEEN '10/1/2010' AND '10/5/2010'
GROUP BY VisitDate

The problem I am trying to overcome is that if there were no visits in a given day, that day does not show in my list.  For example, if there were 3 visits on the 1st, 2 on the 2nd, none on the 3rd, and 5 on the 4th, my result set would look like:

3, 10/1/2010
2, 10/2/2010
4, 10/4/2010

How can I create my query so that I get all days in the date range listed in the result set regardless of whether the VisitCount is zero?

Thanks


SSIS - load table from source text file with multiple record lengths

  

I have a text file that has mulitple records lengths that I need to load into a table.  The file has multiple record types and each record type detemines the length of the record.  Let me try to explain...the file has a balance line 1, multiple detail records, balance line 2, and a trailer record.

The first record is a balance line 1, which has a record length of 144.

Detail records with a record type of inv or crd, will have a record length of 147.  The remaining record types will have a length of 144.

The second to the last record is balance line 2, which has a a record length of 156.

And the trailer record, which has a record length of 162.

Here is a portion of my source file.  I have made each line bold where you can see the rec type.  The record starting with 9999 is the trailer.

077700001BL100000002010100120101008BALANCE FORWARD 0000000000000000001081175D0000000000{0000000000{0000000000{000001081175D00 0000000000{

077701000INV01953172010100120101008GROCERY - DRY 0000000000000000000073504I0000016678K0000000852G0000000000{000000057679D011N 0000093201E010

07779008099000000002010100820101008KC CASH REC. 0000000000000000001081175M0000000000{0000000000{0000000000{000001081175M00 00000000

Could not load an entity when table is existing

  

Need help on this. I'm quite new to NHibernate.

I always get this error when the table is existing in the database:

could not load an entity: 
[Star.CNPL_BusinessObjects.Entities.CNPL.CNPL_AgencyProduct#48][SQL: SELECT cnpl_agenc0_.Id as Id48_0_, cnpl_agenc0_.AgencyID as AgencyID48_0_, cnpl_agenc0_.ProductID as ProductID48_0_, cnpl_agenc0_.CreatedDate as CreatedD4_48_0_, cnpl_agenc0_.CreatedBy as CreatedBy48_0_, cnpl_agenc0_.UpdatedDate as UpdatedD6_48_0_, cnpl_agenc0_.UpdatedBy as UpdatedBy48_0_ FROM CNPL_AgencyProduct cnpl_agenc0_ WHERE cnpl_agenc0_.Id=?]"


The inner exception of the error is this:

Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) 
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) 
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) 
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() 
   at System.Data.SqlClient.SqlDataReader.get_MetaData() 
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader

Load multiple record length text file into table using SSIS

  

I have a text file that has mulitple records lengths that I need to load into a table.  The file has multiple record types and each record type detemines the length of the record.  Let me try to explain...the file has a balance line 1, multiple detail records, balance line 2, and a trailer record.

The first record is a balance line 1, which has a record length of 144.

Detail records with a record type of inv or crd, will have a record length of 147.  The remaining record types will have a length of 144.

The second to the last record is balance line 2, which has a a record length of 156.

And the trailer record, which has a record length of 162.

Here is a portion of my source file.  I have made each line bold where you can see the rec type.  The record starting with 9999 is the trailer.

077700001BL100000002010100120101008BALANCE FORWARD 0000000000000000001081175D0000000000{0000000000{0000000000{000001081175D00 0000000000{

077701000INV01953172010100120101008GROCERY - DRY 0000000000000000000073504I0000016678K0000000852G0000000000{000000057679D011N 0000093201E010

07779008099000000002010100820101008KC CASH REC. 0000000000000000001081175M0000000000{0000000000{0000000000{000001081175M00 00000000

Need to mass load dated files from .txt to sql table - what is the best way?

  

Hi

I have around 500 files which are named file_27072009.txt, file_28072009.txt, file_28072009.txt etc and I need to load them all into a table. What is the easiest way to pick up each of these files in turn and load them into the sql table?

I have access to SISS or sql server mgt studio but am not an expert in either.

Any help would be much appreciated so I am not having to manually open and merge all the files into one before loading.

Thanks so much

Kirsten


Adding exception table to incremental Load with SSIS Lookup task and conditional split

  

Hello,

I have built an incremental load ETL Process to load some flat files with an SSIS Lookup and Conditional Split. I only have one path in the conditional split and that is for New Records.

I have two questions:

1.       For the new records path, I have to check to see if a record exists and I don’t have a single key that is unique, therefore, I have to use a multiple keys to make the record unique.

 

Below is what I have put in the conditional transformation editor for my first output condition:

 

ConfigurationFilter ConfiguredValue PackagePath ConfiguredValueType
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend