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
ADVERTISEMENT
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
Apr 8, 2015
I'm trying to convert a varchar value to datetime format but it throwing below error:
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
here is sample query:
declare @test varchar(20)
set @test ='30/04/2015 23:59:59'
select CONVERT(datetime, @test)
One interesting observation above query run fines in Sql 2008 but not in Sql 2014...
View 3 Replies
View Related
Jun 20, 2007
Hello!
Somebody knows how to convert the data from Clarion (tps) to mdb or mdf files?
Any variants?
Thanks to all!
View 3 Replies
View Related
Jun 25, 2015
fix the below SP which is having cursor output with an alternate logic which can make the SP work.(may be using temp table or any other option)
CREATE PROCEDURE Get_IDS
/*
* SSMA warning messages:
* O2SS0356: Conversion from NUMBER datatype can cause data loss.
*/
@p_uid float(53),
@return_value_argument varchar(8000) OUTPUT
[code]....
View 1 Replies
View Related
Sep 30, 1998
Any ideas on converting integer to a proper date format in SQL server.
View 2 Replies
View Related
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
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
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
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
View Related
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
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
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
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
Jan 28, 2014
I have to write a where condition where i need to compare string with date
I have 2 columns FROMDATE and TODATE with datatype varchar(9)
The strings in the columns looks like YYYYMMDD+'1' or YYYYMMDD+'2' here 1 is Am and 2 is PM
i.e., 201401011 or 201401012
So I need to chop off last character before using them in WHERE condition.
Now I need to write a where condition like [if todays date is in between fromdate and todate columns then return rows.
if FROMDATE column is null it should take minimum date 1900/01/01 if TODATE is null then date should be 9999/01/01
The query i wrote is
select * from Table where ISNULL(SUBSTRING(VALIDTO,1,8),'19000101') > = getdate()
AND ISNULL(SUBSTRING(VALIDTO,1,8),'99991231') < convert(varchar, getdate(), 112)
but it shows no data...
View 9 Replies
View Related
Oct 16, 2015
I am searching for a Powershell script which picks Windows Server names from SQL server table(eg: Instance.DB.tbServerList) & writes last reboot date to SQL server table(can be same or different table).
View 6 Replies
View Related
May 6, 2014
So let's say I have a table Orders with columns: Order# and ReceiptDate. Order#'s may be duplicated (Could have same Order# with different ReceiptDate). I want to select Order#'s that go back 6 months from the last ReceiptDate for each Order#.
I can't just do something like:
SELECT *
FROM Orders
WHERE ReceiptDate >= add_months(date,-6)
because there could be Order#'s whose last ReceiptDate was earlier than 6 months ago. I want to capture all of the instances of each Order# going back 6 months from each last ReceiptDate relative to each Order#.
View 9 Replies
View Related
Aug 26, 2014
Im trying to change the date format to DD-MM-YYYY serverwide.
i have changed to british english in the server settings, and my user also has british english set but the date is still YYYY-MM-DD.
how i can change this?
View 9 Replies
View Related
Mar 31, 2015
I have this script that I'm trying to filter the results of the Job History to the day prior at 1800 hours.
It return dates prior to what I have in the WHERE clause.
What should the WHERE Clause look like?
USE msdb
Go
SELECT j.name JobName,h.step_name StepName,
CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) RunDate,
STUFF(STUFF(RIGHT('000000' + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') RunTime,
h.run_duration StepDuration,
case h.run_status when 0 then 'failed'
[code]....
View 1 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
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
May 18, 2015
I would like to put a Clustered Index on a date column in a current heap, but one question/concern.This heap every month has thousands of rows deleted and even more added later. How much of an issue will this cause the Clustered Index as far as page splits? I was thinking Fill Factor of 70%.I would normally just test and still will on Dev box, but my Dev box is much smaller than production as far as power.
View 6 Replies
View Related
May 26, 2015
When I run this query:
SELECT orderno, *
FROM _order
WHERE order_date >= '5/14/2015 00:00:00'
AND order_date < '5/15/2015 11:59:59'
ORDER BY order_date asc
I get a result of:
A1G7222015-05-14 13:00:11.143
A1G7232015-05-14 13:33:35.407
A1G7242015-05-14 13:39:16.657
A1G7252015-05-14 14:25:43.507
A1G7262015-05-14 14:29:18.050
A1G7272015-05-14 15:38:12.263
But I know there is one more record that falls into 05/15/2015
A1G7282015-05-15 12:26:52.807
Can you see what I am missing in my query in order for me to retrieve the missing record A1G728?
View 9 Replies
View Related
Jul 3, 2015
This is a piece of my code:
SELECT TOP 1000 *...
case when Rownum<= datediff(day, salesdate, baseenddate)
then DATEADD(mm, RowNum, salesdate) /*error at this point*/
else 0 end as subscriptionrowdate
FROM Subsrow
Rownum is an integer type. DATEADD is the part when the error is but i dont know how to convert this to int
View 4 Replies
View Related
Aug 21, 2015
What I am trying to accomplish is to make a few extra columns with specified date ranges.
I have FY14 date range in the parameters at the top .. I would like to add a FY 15 column so the year would move up by 1.and also I need to add 2 more columns Prior year current month and This year, current month.
<code>
DECLARE @Fy14_start datetime
DECLARE @Fy14_end datetime
SET @Fy14_start = '2013-07-01'
SET @Fy14_end = '2014-06-30'
SELECT x.ACCOUNT_NAME, X.STATUS_CODE, COUNT(X.PATIENT_CODE) AS FY14
[code]....
View 2 Replies
View Related
Oct 20, 2015
I've used some info on here to generate random dates within a given range and also random times - independently they work fine, but I can't seem to join them into a single field of datetime. I'm not sure why. The following snippet works fine as two independent fields:
select CAST(CAST(ABS(CHECKSUM(NEWID()))%(780)+(33968) AS DATETIME) as DATE) as theDate,
CAST(CAST(DATEADD(milliSECOND,ABS(CHECKSUM(NEWID()))%86400000 ,'00:00') AS TIME) as varchar(50)) as theTimeBut when I try to make it a single datetime field:
select CAST(cast(cast(CAST(ABS(CHECKSUM(NEWID()))%(780)+(33968) AS DATETIME) as date) as varchar(50)) + ' ' + cast(CAST(CAST(DATEADD(milliSECOND,ABS(CHECKSUM(NEWID()))%86400000 ,'00:00') AS TIME) as varchar(50)) as varchar(50)) as datetime)
Which returns with: Conversion failed when converting date and/or time from character string.
So what I am really looking for is a way to join those two values into a single datetime field... Or failing that that how to generate random dates within a range including random times...
View 9 Replies
View Related
May 21, 2014
I have a dynamic SQL query that uses various indexes and views.
When a user wants to filter records based on last one day, last one week, last 30 days ...etc.. i use the following code:
@date is an integer.
begindate is datetime format.
begindate > cast(((select dateadd(d,@Date,GETDATE()))) as varchar(20))
The above code slows my query performance by at least 400%!
Would it be faster to add a computed column to my table, using the suggested method in the link below?
[URL] ....
Then i could add an indexed view to improve performance, or can this be done another way?
View 9 Replies
View Related
Aug 18, 2014
I have date field in table and data contain 2014-08-09 11:13:03.340
when I use smalldatefield I am getting 2014-08-09 11:13:00 I should have got 2014-08-09 11:13:03
Why the 03 is trimming in smalldatefield.
how do I use select query to get 2014-08-09 11:13:03 from original Date records
View 1 Replies
View Related
Jan 10, 2015
Looking for query that lists all databases, in an instance, that are not accessed before a given date (e.g., not accessed before December 31, 2014)?
View 6 Replies
View Related
Oct 6, 2015
Is it possible to find out the last executed date for any stored proc in the database using system tables or writing any other query.
View 2 Replies
View Related
Apr 4, 2006
My Colleague says that Clarion is better DataBase for some scenarios than SQL Server 2005. Can someone help me; is there some comparison table or study on that? In what cases may that be truth( in none i think ).
Please help!
View 1 Replies
View Related
Sep 4, 2006
Does anyone here know or have experience on doing replication between SQL server 2000 and Clarion ( .tps ).
Hope anyone can give me any advice / guide / reference / etc.
Thanks in advance.
View 4 Replies
View Related
Jun 3, 2015
Here's my statement below. What I'm trying to get is joining the name column in master.sys.databases with a sub query for the database name, file location and backup start date from the MSDB database. The reason for this, if a new database has never been backed up, It should be returning as a NULL value, which is my goal. However, I'm getting multiple results for the backups.
select CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,a.name,File_Location=b.physical_device_name,backup_start_date=max(backup_start_date)
from master.sys.databases a
left join(select c.database_name,backup_start_date=max(backup_start_date),b.physical_device_name
from msdb.dbo.backupmediafamily b join msdb.dbo.backupset c on c.media_set_id=c.backup_set_id
where c.type='D'
[Code] .....
View 8 Replies
View Related
