SQL Server 2012 :: Serial Date Conversion

Nov 4, 2014

I am in need of converting serial date to regular date ie...735510.40461 and only need the hours, minutes and seconds, I have used the examples I've seen on different forums,

DATEADD(d, -1, DATEADD(m, DATEDIFF(m, '1900-1-1', getdate()) + 1, '1900-1-1'))

View 9 Replies


ADVERTISEMENT

SQL Server 2012 :: Conversion Failed When Converting Date

Oct 30, 2014

I have been trying to convert datetime but keep getting this error(Conversion failed when converting date and/or time from character string.It works just fine if I don't use execute sp_executesql,.

<code>
DECLARE @tablename AS nvarchar(max)
DECLARE @SQLQuery AS NVARCHAR(MAX)
DECLARE @ParameterDefinition AS NVARCHAR(100)
DECLARE @startdate datetime

[code]....

View 5 Replies View Related

SQL 2012 :: Add Serial Number Column And Sno 1 To 40000

Mar 28, 2014

Add sno 1 to end of the coloum

I have table like ...

400000 laks rows in my table

name address id location
raju hyd 6789 hyd
vamshi vizag 9876 hashinapur

o/p like

I want insert sno coloum and sno 1 to 400000

sno name address id location
1 raju hyd 6789 hyd
2 vamshi vizag 9876 hasthinapur
3
4
5
6
..
.
.
.
.
40000

View 2 Replies View Related

SQL 2012 :: Add Serial Number Based On Group By

Mar 23, 2015

I need a new field added 'Field1' which will add SEQUENCE number 1,2,.. based ON GROUP BY MasterID..AND another field TotalCount which will COUNT total masterID (here it will be 2)

CREATE TABLE #Temp1
( MasterID INT,
ClientName VARCHAR(10),
ProductName VARCHAR(50)

[code]...

View 0 Replies View Related

Flat File Text Date Conversion To SQL Server Date Comments And Suggestions

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

Transact SQL :: Two Row Serial Number Date Compare And Take More Than 5 Hours Between Two Rows

Jul 1, 2015

A vehicle loading confirm after that what time its gated out so i want to take the time duration between finish loading and gate out, find sample table records , i want to take more than 5 hrs difference between finish loading and gate out.

tld_tripno
tld_sno
tld_activitycode
tld_location
tld_actualdate

TLM3004242015

[Code] .....

I want to take the result like this 

Tld_tripno
Finish Loading
Gate Out
Date and Time difference

TLM3004242015
2015-05-11 19:58:00
2015-05-12 08:42:00
12:44:00

View 10 Replies View Related

SQL Server Date Conversion

Sep 30, 1998

Any ideas on converting integer to a proper date format in SQL server.

View 2 Replies View Related

Date Conversion In SQL Server

May 17, 2007

Hi folks,

Here are the fields I have

eventDate = smallDateTime 2005-12-12 00:00:00
eventTime = varchar(20) 1:00:00
newEventDate = dateTime [desired result: 2005-12-12 1:00:00]

When I run the following script:
update healthEvent
set newEventDate = cast(substring(convert(varchar,eventDate,120),1,10 )+' '+eventTime as DateTime)

I get "Syntax error converting datetime from character string." error.

Any ideas why?

Thanks!

-Parul

View 4 Replies View Related

Date Conversion In SQL Server 2005

Apr 25, 2008

Hello Everyone, thank you for taking the time to read my post. I'm creating a view in SQL Server 2005 to base a report on Crystal Reports XI. I've been trying to figure out how I can convert a date field in the format YYYYMMDD to MM/DD/YYYY. I'm not quite sure about the steps I need to take to accomplish this since I'm pretty new to this. The date is stored as an Int on the database, and I've tried converting it directly to the DATETIME data type like this: CONVERT(DATETIME, datefield,101) but I'm getting an error saying illegal data type conversion. Thanks a lot for your help, I really appreciate it.

MS

View 7 Replies View Related

Oracle To Sql Server Date Conversion

Mar 12, 2008

Hi Gurus,
I need to convert this statement to sql server.
SELECT to_date('24-08-2007 13:11:12','dd-mm-yyyy hh24:mi:ss'),to_date('24-aug-2007 13:11:12','dd-mon-yyyy hh24:mi:ss') from dual

can anyone help please.

View 1 Replies View Related

SQL Server 2012 :: Rows To Columns Conversion

Aug 28, 2014

I would like to reach out in converting rows to cols.

Format:
NodeName|upsBasicIdentName|upsBasicIdentModel | upsAdvIdentSerialNumber | upsAdvIdentDateOfManufacture | upsBasicBatteryLastReplaceDate

I have this query. the values of each are under status.

SELECT
Nodes.Caption AS NodeName, CustomNodePollers_CustomPollers.MIB AS MIB, CustomNodePollerStatus_CustomPollerStatus.Status AS Status

FROM

((Nodes INNER JOIN CustomPollerAssignment CustomNodePollerAssignment_CustomPollerAssignment ON

[Code] ....

View 3 Replies View Related

SQL Server 2012 :: Conversion Failure Varchar To Int

Dec 15, 2014

How do I pass a variable to an INT calculation?

CREATE PROC CLEAR_MY_TABLE
@TableStat varchar(30)
AS
If OBJECT_ID('MyDB.dbo.' + @TableStat + '') is not null
BEGIN
---PRINT 'I FOUND THE TABLE'
DECLARE @count INT = -1;
SELECT @count = (Select COUNT(*) FROM [dbo]. + @TableStat);
IF (@count > 0)
BEGIN
TRUNCATE TABLE @TABLESTAT
END

View 8 Replies View Related

SQL Server 2012 :: Varchar To Datetime Conversion

Aug 14, 2015

I have column moddat which is of varchar(10,null)

Here is my data:
20020415
20020508
19991104
19990701
20040514
20021112
20020124
19990628
20020514
20010822

I want those data in this format YYYY-MM-DD

How to convert varchar to datetime?

View 2 Replies View Related

SQL Server 2014 :: Clarion Date Conversion

Oct 7, 2015

The database for our software stores dates and times in clarion format. I'm trying to write some custom reports in T-SQL and I need to convert these dates but it's giving me a lot of trouble. How do I query the dates and times and have the results shown as a "regular" date/time? Below is what I have so far which is really the very beginnings of this report. Basically on the results/output I need the ClarionDate and ClarionTime to be shown as typical date/time columns. I did some research on my own but I'm having a hard time grasping this. I believe they have to be pulled into a temp table and then converted?

SELECT A1.DATEOFACCESS ClarionDate, A1.TIMEOFACCESS ClarionTime, A2.NAME Event
FROM dbo.History A1
JOIN dbo.SysEvents A2
ON A1.RSVD_Action = A2.RSVD_EVENTTYPE

View 2 Replies View Related

Using VB 6.0 And SQL Server: Date Causes Datetime Conversion Error

May 10, 2008

I'm using a VB 6.0 front end with a date variable
that throws a SQL 2005 datetime conversion error in the following UPDATE query:

UPDATE MYTable
SET Feild1 = 'VBVar1', Field2='VBVar2'
WHERE MydateField = 'VBdateVar'

I'm guessing that I probably am missing some appropriate method for passing the date datatype variable to SQL


Can anyone help me

Thanks

View 7 Replies View Related

Transact SQL :: Server Date Format Conversion

May 12, 2015

I am facing an issue

1:- From Source system ,I am getting dates like --- 

2014-Q3
2014-Q2
2014-09

As per my requirement, I need to convert this time to dates like 

1:- 2014-Q3  ---   last day of quarter 3 of 2014 .  
2: 2014 -02 -- last day of feb 2014

Converting such format  to the dates I expect...

View 8 Replies View Related

Conversion Of Oracle Date Time To Sql Server Date Time In SSIS

Jun 30, 2007

This is driving me nuts..



I'm trying to extract some data from a table in oracle. The oracle table stores date and time seperately in 2 different columns. I need to merge these two columns and import to sql server database.



I'm struggling with this for a quite a while and I'm not able to get it working.



I tried the oracle query something like this,

SELECT
(TO_CHAR(ASOFDATE,'YYYYMMDD')||' '||TO_CHAR(ASOFTIME,'HH24:MM : SS')||':000') AS ASOFDATE

FROM TBLA

this gives me an output of 20070511 23:06:30:000



the space in MM : SS is intentional here, since without that space it appread as smiley



I'm trying to map this to datetime field in sql server 2005. It keeps failing with this error

The value could not be converted because of a potential loss of data



I'm struck with error for hours now. Any pointers would be helpful.



Thanks

View 3 Replies View Related

SQL Server 2012 :: Conversion Failed When Converting Varchar Value

Dec 19, 2013

Within in Visual Studio 2012 solution, I have several projects, one of which is a Database project. I am defining several tables. The one in question is:

CREATE TABLE [dbo].[tblKppHierarchyPcl]
(
[ID] NUMERIC(18,0) NOT NULL,
[Name] VARCHAR(500),
[PartStructureKey] NUMERIC(18,0) NOT NULL,
[PartNumber] VARCHAR(500) NOT NULL,
[ParentPartNumber] VARCHAR(500) NULL,

[code]...

Error SQL72014: .Net SqlClient Data Provider: Msg 245, Level 16, State 1, Line 76 Conversion.failed when converting the varchar value 'Coolant Quick Disconnect' to data type int.So it has a problem with inserting 'Coolant Quick Disconnect' into the Name column. The Name column is CLEARLY a varchar column but somehow it thinks it's an int column.

View 8 Replies View Related

SQL Server 2012 :: Pattern Matching And Data Conversion

Feb 20, 2014

I have a table ("MyData") with string columns that have nvarchar data that looks like this:

ColA
--------
42/90
78/109

I plan to do a mathematical grouping on the numerator (eg: 0-10,11-20,21-30, etc... ), So I need to grab the numerator and turn it into an int. For reasons I can't get into, I have to do this in pure T-SQL.

So my question: How would I write a select statement that regex pattern matches "^([0-9]+)/" on ColA and returns integers instead of text?

E.g. THis Query:

SELECT [magic t-sql syntax] as Converted_ColA from MyData

should return this set of int values:

42
78

View 8 Replies View Related

SQL Server 2012 :: Implicit Conversion Comparing INT Column To 0?

Sep 15, 2015

In a stored procedure, the following code is causing a huge read and CPU load when it really shouldn't. The @IDParameter below is coming in as a parameter to the proc.

Here's the snippet of code where the problem is coming in:

DECLARE @ID INT;
SET @ID = (SELECT ID From OtherTable WHERE FKID = @IDParameter);
SELECT COUNT(*)
FROM LargeTable

WHERE MostlyZeroID = @ID AND MostlyZeroID > 0Most (90+%) of the MostlyZeroID rows are 0 (hence the name) but regardless of distribution this should evaluate with minimal work on SQL Server's part to 0. However, when this was run, it is using a ton of CPU and doing a ton of Reads as it seeks through the entire index. When I look at the execution plan, I see under the seek predicate a Scalar Operator(CONVERT_IMPLICIT(int,[@1],0)) which is what is destroying the performance.

I've confirmed that the MostlyZeroID column in the LargeTable is defined as an INT NOT NULL. I also tested the scenario outside the stored procedure without any variables as the following to make sure it wasn't some kind of strange parameter sniffing scenario:

SELECT COUNT(*)
FROM LargeTable
WHERE MostlyZeroID = 0 AND MostlyZeroID > 0

However, this query also did the implicit conversion. I then tried this out on a temp table populated with a similar number of records (100 million) with a similar distribution and I didn't get the implicit conversion (I got a constant scan as I would've expected) when I did this:

SELECT COUNT(*)
FROM #TestTable
WHERE MostlyZero = 0 AND MostlyZero > 0

I also tried the same on several other tables that are set up similarly (large amount of zeros in an INT column) and I always got a constant scan and didn't do an implicit conversion.

why the query engine is interpreting this 0 as something other than an INT and doing an implicit conversion when getting the count in the scenario above? What can be done to protect against it? In the above scenario, an IF @ID > 0 statement was placed before the code including the count since there was no reason to even run the code if the @ID was equal to zero.

View 9 Replies View Related

SQL Server 2012 :: How To Match Two Different Date Columns In Same Table And Update Third Date Column

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

SQL Server 2012 :: Calculate Number Of Days Based On Computer System Date And Due Date Row

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

SQL Server 2012 :: Script To Specify Date Field To Use For Activity Date

Jun 2, 2014

I'm writing a view to check record counts in a table that has numerous datasets and therefore various "Activity Dates". Is it possible as part of the SQL statement to have a CASE statement for example so that it can identify the field to use as the activity date?

The field to use is being identified using a seperate table so at the moment I have CASE WHEN FieldToUse = '2' THEN MapCol ELSE '[Activity_Date]' END, where FieldToUse = '2' identifies the date field to use and the MapCol data is the field name to be used as the activity date.

Is this even possible?

View 3 Replies View Related

SQL Server 2012 :: Bulk Load Data Conversion Error (truncation)

May 15, 2014

Is there a switch I can use to force a bulk insert and if data is truncated, I'm good with that. The truncated data, in this case, is not data I can use anyway if it is long enough to be truncated.

I need to keep the field at VARCHAR(23) and if I expand it, I won't be able to join on it after the file load completes. I'd like the data to be inserted (truncated if need be) and then I'll deal with the records that are truncated after I load the file.

View 5 Replies View Related

SQL Server 2012 :: Conversion Failed When Converting Varchar Value To Data Type Int

Aug 12, 2014

I am doing a Case statement to create a unified field for account type and vendor, in the code below I am receiving the error in the subject, because the account numbers have alpha characters in the string, I need to make them as OTHER if the first 2 left chars are Alpha, I thought how I have ISNUMERIC would do that but I am still getting the error.

I am also including example of how the account_numbers are formatted.

R222209
R222220
R222222
R222212
R221123
F707768

[Code] .....

View 5 Replies View Related

Date Function - Conversion Failed When Converting Date And / Or Time From Character String

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

Transact SQL :: Due Date - Conversion Failed When Converting Date And / Or Time From Character String

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

Change SQL Server Serial Number

Jul 23, 2005

Good morning !Anyone knows how can i change a SQL Server Serial number without uninstallit ?Thanks in advancedJLuis

View 1 Replies View Related

Serial Number In Query, In MS Access And SQL Server

Jan 16, 2008

im trying to run a query, my first column should be serial number based on results, how can i do that for example

qry = "Select products, sum(prod_price), sum(prod_sellingprice) from sales group by products"

or

qry = "Select products, sellingperson from sales where seldate=#" & date() & "#"

i want first column of both queries as serial number based on results of query.

View 3 Replies View Related

SQL Server 2012 :: Date Between In Procedure

Dec 19, 2013

I need procedure query for two date between fromdate and todate

alter procdure date
declare @Fromdate varchar(10)
declare @todate varchar(10)
as begin
select * from master
where convert(varchar(10),@Fromdate,121) between CONVERT(varchar(10),@Todate,121) and CONVERT(varchar(10),rr.Original_Date,121)
end

View 3 Replies View Related

SQL Server 2012 :: Add Month To A Date

Jun 3, 2014

I am trying to add month to a date. Here is my code

declare @CollectionDate date='10-28-2014'
select @CollectionDate
;WITH CTemp AS (
SELECT TransactionDate=CAST(@CollectionDate AS DATE) ,RemainingTransaction=1
UNION all

[Code] ....

It is working fine. But when I am giving date '10-30-2014' it shows me the error

Msg 241, Level 16, State 1, Line 3
Conversion failed when converting date and/or time from character string.

I can understand the problem that it is for the month of February. But How do I overcome the situation?

View 3 Replies View Related

SQL 2012 :: How To Get Last Run Date On All Databases On One Server

Oct 23, 2015

I know how to get the last run date on all the databases on one serveri have a lot of servers and i was wanting a way of getting the last run date across all servers.

View 3 Replies View Related

Date Conversion

Aug 6, 2005

i do have date problem in sql server, i m using DD/MM/YYYY date format, & passing it to insert & update stat...& compairing it with data in table, which is not working properly, how to convert dd/mm/yyyy to mm/dd/yyyy or yyyy-mm-dd
hoping for solution soon, thanx
murli ......

View 7 Replies View Related







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