I need to convert a vb function into a user-defined function in a stored procedure. The vb function converts a long datatype into Date datatype.
Here is the VB function:
Function LongToTime(lTime As Long) As Date LongToTime = (lTime 10000) / 24 + ((lTime Mod 10000) 100) / 1440 + (lTime Mod 100) / 86400 End Function
The function is used to convert a timestamp(hhmmss) into a more readable format. Would it be possible to create a function similar to this in an SQL stored procedure?
I need to handle this conversion in SSIS and not on oracle.
The following expression is executed on a datatype of dt_str with a length of 8000.
SUBSTRING((DT_STR,8000,1252)Column_name,1,8000)
Records longer then 4000 bytes take an error path
The next expression with 4000 bytes works but there is truncation.
SUBSTRING((DT_STR,4000,1252)Column_name,1,4000)
Basically I need to know how to cast a text or ntext into a varchar or nvarchar using ssis but I need to capture the first 8000 byes without truncation.
is this possible?
Using SSIS Reading From oracle I can convert to a text or ntext field but I am having a hard time going directly to a varchar.
I have a table with a datetime field 'TheDate'. Currently dates are stored as 'mm-dd-yyyy 00:00:00'. Is there a way to get just the month, day and year parts, '01/01/2008' into the field without changing the field data type to varchar? I'm asking because when I do this:
update TheTable set TheDate = substring(convert(varchar,TheDate,101),1,10)
I'm still getting a date in the format 'mm-dd-yyyy 00:00:00' stored in the table. I'd like to be able to lose the time portion, but I'd like to be able to keep the datetime datatype for date math purposes. Can it be done?
Hi: I have a column call Date_Sent (28/02/2004)(dd/mm/yyyy) format as varchar at beginning. I want to convert to other column as datetime. I use Query like: SELECT CAST(SUBSTRING(Date_Sent,1,2)as int) + '/' +CAST(SUBSTRING(date_sent,4,2) as int) + '/' +CAST(SUBSTRING(DATE_SENT,7,4) as int) From MyTable It is not working, anybody can give me some advise! thanks!
hi, How do i convert a varchar field into the datetime data type? the reason i need this lies in the requirement that in the earlier data base the column that is hlding the date value is having the data type as varchar. and in the new design the column data type is datetime. i am using sql scripts for the data migration from the older design to the newer and got stuck with this datetime convertion issue. do let me know the best possible solution.
following are the sample data that is theer in the older table for the date.
12/12/2003 1/13/2007 01132004 1-1-2004 1.2.2001
there is no uniformity of the data that is stored currently.
Can anyone help me on this!I've got more than a 1000 records in a SQL server database.The problem is that the the date field is set to varchar, and that gives a lot of trouble. (for example by sorting a table, it's a mess)How can i make sure that i will have a table with the date field set to datettime en that those 1000 records still will be in it. thanks in advance!
I have two tables Costtable (Id,ResourceId, Amount,Date) and ResourceTable (ResourceId,Name) which shows output as below.
I want to show 0 amount for rest of the name in case of September. For e.g. if rest of the Resources does not appear in cost table they should appear 0 in amount
My Desired output
My current query
SELECT RG.Id AS Id, RG.Name AS Name, ISNULL(SUM(AC.Amount), 0) AS Amount, RIGHT(CONVERT(varchar(10), AC.[Date], 105), 7) AS [YearMonth]
Table 1: AddressBook Fields --> User Name, Address, CountryCode
Table 2: Country Fields --> Country Code, Country Name
Step 1 : I have created a Cube with these two tables using SSAS.
Step 2 : I have created a report in SSRS showing Address list.
The Column in the report are User Name, Address, Country Name
But I have no idea, how to convert this Country Code to Country name.
I am generating the report using the Layout tab. ( Data | Layout | Preview ) Report1.rdl [Design]
Anyone help me to solve this issue. Because, in our project most of the transaction tables have Code and Code description in master table. I need to convert all code into corresponding description in all my reports.
I have a question that has a co-worker and myself confused. We are using the SQL Server Enterprise Manager. When I select return all rows in my tables, any content that is too long will appear as <Long Text> on my co-workers machine, but the column is blank on my machine. Is there a property or configuration that enables/disables <Long Text>? I am confused as to why it appears on other machines, but not mine.
I have a table containing URLs. I want to be able to look up an URL veryfast, so I used an nvarchar to store the URL, and put an index on it(maybe naive).Anyway, I bump into:"The index entry of length 911 bytes for the index 'UQ__URL__1367E606'exceeds the maximum length of 900 bytes."What's the best way to handle this? I want to do the look up fast. Theonly thing I could think up was adding an extra column containing a digestfor the URL, and look up all URLs with the same digest, *and* having thesame value (which could give either 1 or 0 results).I am new to MS SQL, so I might describe a silly solution, basically I wantto look up URLs to ID the fastest way possible.--John MexIT: http://johnbokma.com/mexit/personal page: http://johnbokma.com/Experienced programmer available: http://castleamber.com/Happy Customers: http://castleamber.com/testimonials.html
Hi I want to show only the month names in user selection criteria. If we create a report parameter of type datetime it will let user to select day too. I want only month and year to be selelected from dateteim control...
I am inserting date and time data into a SQL Server 2012 Express table from an application. The application is providing the date and time as a string data type. Is there a TSQL way to convert the date and time string to an SQL datetime date type? I want to do the conversion, because SQL displays an error due to the
My date and time string from the application looks like : 3/11/2014 12:57:57 PM
how would i take the query and show just the current months unique logins
SELECT DISTINCT YEAR(Login_Time) AS Year, MONTH(Login_Time) AS Month, COUNT(*) AS Login_Cnt FROM Login WHERE Login_Time >=#10/1/2005# GROUP BY YEAR(Login_Time), MONTH(Login_Time) ORDER BY YEAR(Login_Time), MONTH(Login_Time)
I've two tables that I've made from some query subsets. Each table has a varchar field with notes/memos and I want to concatenate the fields into one long field.
The problem I'm running into is that when I run the query to check the concatenation, the field is truncated maybe 256 chars in.
I tried converting and casting the field as nvarchar 4000, and I've also done the same for the fields in the two tables, but that doesn't seem to help.
I can query for the fields from each table and none of them are truncated by themselves. It only happens after I concatenate them.
I've created a new table and inserted the results into it, but the field in it is also truncated.
I need to modify existing table in my database to varchar(max) from varchar(2000)
This table contains 30 million plus rows and has more than 70 columns.
now when i am running alter command for this it take too long(more than 9 mins) which is not acceptable. . Is their any way to reduce this execution time
Following is the query i am using for this
ALTER TABLE Receipt ALTER COLUMN CUSTOM VARCHAR(MAX) NULL
Please let me know if you have any suggestion to improve this
I m creating P&L(profit and Loss ) Reports of accounts its consists of 2 levels
in level2:
my procedure display the output of (Actuals in lakhs)
RESPONSIBILITY DEPT CATEGORY CURRENT YTD ACTUALS SALES Sales Net Sales 444.65 Sales Sales LESS TRD 22.55 SALES NET RETURNS NET RETURNS 422.10 (net sales - TRD) Finance LESS ED LESS ED 40 Sales Totals Sales 382.10(RETURNS - ED)
(only calculation for above dept only remaining dept values display sum of relvenat accounts ,and if i click the category relvent account codes shown here)
Materials .... ... .. production ..... ............ ........
i made a procedure for above
create procedure Pl_level2
@fmdate datetime, @todate datetime, @category varchar(200) as begin create table #temp1
[code]....
like i m inserted so many accounts in temp tables .
IF (@category IS NULL or @catagory=' ' ) begin select responsibility, dept, category, round(Max(Actuals)/100000,1,2) as Actuals from #temp
[code]....
here i can display only current YTD only how to display previous year (13-14) YTD in level1
Can we show the data of a report for previous month? The report is supposed to run montly basis. But once it is run, it shows the data only for the previous month.
we can show it if its only for previous day. In this case it is like this;
cdtable.SubmittedDate = GETDATE () - 1
But I dont find function like GETMONTH() or smthing.
Can we show the data of a report for previous month? The report is supposed to run montly basis. But once it is run, it shows the data only for the previous month.
we can show it if its only for previous day. In this case it is like this;
cdtable.SubmittedDate = GETDATE () - 1
But I dont find function like GETMONTH() or smthing.
Hi,I have a text file that contains a date column. The text file will beimported to database in SQL 2000 server. After to be imported, I wantto convert the date column to date type.For ex. the text file look likeName dateSmith 20003112Jennifer 19991506It would be converted date column to ydm database in SQL 2000 server.In the table it should look like thisName DateSmith 2000.31.12Jennifer 1999.15.06Thanks in advance- Loi -