|
Don't store dates as strings, and don't use string methods to manipulate dates. If you have any control over this database, you should change the EP_SCAN_DATE column to be a datetime.
You haven't specified which DBMS you're using. Assuming MS SQL, you can use something like this:
SELECT DateAdd(hour, 9, DateDiff(dd, 0, YourDateTimeColumn))
The DateDiff(dd, 0, X) will return just the date part of X, with the time set to midnight. You then use DateAdd to add as many hours, minutes and seconds as you require.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
HI, i used MS SQL server 2008
|
|
|
|
|
In that case, you might want to consider changing the column type to datetime2(0)[^], which is two bytes smaller than a datetime.
Alternatively, if you never need the time portion of the column, you could change the type to date[^], which is even smaller. You'd then need to cast the value to datetime2(0) before adding the time:
SELECT DateAdd(hour, 9, Convert(datetime2(0), YourDateColumn))
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Let me reinforce what Richard has said, NEVER store date as text/varchar/char, ALWAYS use the correct data type.
Text will give you endless problems whenever you try and manipulate the data. The date and datetime data types will give you the tools to manipulate the data easily (not simply it can take a bit of thinking to work out how to achieve a given result).
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello,
I recently had the oracle 11g Express Crashed.
I have copied the .dbf files from C:\oraclexe\app\oracle\oradata\XE to other folder and reinstalled Oracle 11g Express edition.
The files are
Control.dbf
sysaux.dbf
system.dbf
TEMP.DBF
UNDOTBS1.DBF
USERS.DBF
I want to remount the old database files, so that I can recover database.
Can you please tell me how can I do this?
-
Prathamesh
|
|
|
|
|
|
Maciej Los,
Thanks for your comment, But you can see the Question is Listed on OTN.
The similar Question you referred is also mine.
I am still in search for the solution.
-
Prathamesh
|
|
|
|
|
Hello,
I am using win XP with oracle 11g Express edition. I have a customized application written in c#.net and Oracle 11g express as backend.
It was all working fine till today, But now I am getting error.
While opening c# application I am getting ORA-1033 Oracle initialization or shutdown in progress
and if I try from command prompt.
sqlplus system/system123
Error : ORA-12560: TNS: protocol adapter error
tnsnames.ora content
XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = user-pc)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
Oracle Services in services.msc
NAme Status
oraclejobschedulerXE -
OracleMTSRecoveryService - Started
OracleServiceXE - Started
OracleXEClrAgent - Started
OracleXETBSListener - Started
TNSPING O/p.
C:\Documents and Settings\User>tnsping localhost
TNS Ping Utility for 32-bit Windows: Version 11.2.0.2.0 - Production on 14-JUL-2
013 15:57:23
Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:
C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\sqlnet.ora
Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTO
COL=TCP)(HOST=127.0.0.1)(PORT=1521)))
OK (30 msec)
C:\Documents and Settings\User>
content of listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = user-pc)(PORT = 1521))
)
)
DEFAULT_SERVICE_LISTENER = (XE)
Listener Status :
C:\Documents and Settings\User>lsnrctl status
LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 14-JUL-2013 16:00
:46
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 - Produ
ction
Start Date 14-JUL-2013 15:07:14
Uptime 0 days 0 hr. 53 min. 34 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Default Service XE
Listener Parameter File C:\oraclexe\app\oracle\product\11.2.0\server\network\a
dmin\listener.ora
Listener Log File C:\oraclexe\app\oracle\diag\tnslsnr\user-pc\listener\a
lert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=user-pc)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 3 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "xe" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...
The command completed successfully
command :lsnrctl services
C:\Documents and Settings\User>lsnrctl services
LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 14-JUL-2013 16:02
:12
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 3 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
"ORACLE SERVER" established:0 refused:0 current:0 max:25 state:ready
CLRExtProc
(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\NTN_438_FC77BB0E_BBD1.ORA))
"ORACLE SERVER" established:0 refused:0 current:0 max:25 state:ready
CLRExtProc
(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\NTN_438_FC77BB0E_B074.ORA))
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "xe" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:7 refused:0 state:ready
LOCAL SERVER
The command completed successfully
Tried login using sysdba
C:\Documents and Settings\User>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun Jul 14 16:03:30 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-12560: TNS:protocol adapter error
Enter user-name:
tried connecting from sql command line
SQL*Plus: Release 11.2.0.2.0 Production on Sun Jul 14 16:04:32 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL> shutdown immediate;
ORA-01012: not logged on
SQL> connect system/manager321
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0
SQL>
Can you please suggest a solution? I have no fresh database backup.
-
Prathamesh
|
|
|
|
|
Hi,
I have table like below
col1
5
10
15
20
I need result like
Col1 Col2
5 05--9
10 10--14
15 15--19
20 20-24
Plz help
Thank You
Anil Banjare
|
|
|
|
|
How do you define ladder for the last value??
i.e. how do you get 24 in last row??
Apart from that, required query is -
SELECT COL1, NVL2(NEXT_COL1, COL1 || '--' || (NEXT_COL1 - 1), NULL) COL2
FROM (
SELECT COL1
,LEAD(COL1, 1, NULL) OVER (ORDER BY COL1) AS NEXT_COL1
FROM (SELECT LEVEL*5 COL1 FROM DUAL CONNECT BY LEVEL < 5)
)
;
And the output will be -
COL1 COL2
5 5--9
10 10--14
15 15--19
20 NULL
Thanks & Regards,
Niral Soni
|
|
|
|
|
USE tempdb ;
GO
DECLARE @ladderTable TABLE ( Col1 INT ) ;
DECLARE @incrementValue INT = 0 ;
INSERT INTO @ladderTable ( Col1 )
VALUES ( 5 ),
( 10 ),
( 15 ),
( 20 ) ;
SELECT TOP 1
@incrementValue = Col1
FROM @ladderTable
ORDER BY Col1 ASC ;
SELECT st.Col1,
CAST(st.Col1 AS VARCHAR(20)) + '-'
+ CAST(ISNULL(dt.Col1, st.Col1 + @incrementValue) - 1 AS VARCHAR(20)) AS LadderSequence
FROM @ladderTable AS st
LEFT OUTER JOIN @ladderTable AS dt ON st.Col1 < dt.Col1
AND ( st.Col1 + @incrementValue ) >= dt.Col1 ;
GO
Thanks,
Karunakar
|
|
|
|
|
I need to give an introductory database course to a bunch of people who generally are able to write simple SQL queries, but still mainly use files to save stuff. (fits files, ascii files, csv files, ...)
My course starts with presenting some pro's and con's, explaining the basic structure (tables, links, constrains, keys, indexes, ...) and diving into some basic SQL. A second lesson (still under development) would be coding against databases (creating a connection, SQL injection, ...)
I also included a lab with exercises (for lesson 1, lesson 2 to be done).
I looked at some tutorials on the internet, but still would like some advice on how to best bring this topic.
thanks in advance.
|
|
|
|
|
V. wrote: A second lesson (still under development) would be coding against databases (creating a connection, SQL injection, ...)
I'd recommend explaining sprocs and functions, moving to Xml in the third lesson. Creating a connection to the database from a non-sql language has little to do with SQL.
What's their role? If design comes into play, you could reserve a week for the topic of database-normalization. There's examples on structures that break normalization on the wikipedia[^]; the update-anomaly is always a nice intro as people recognize it from the real world 
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Thanks! that already helps !
|
|
|
|
|
After reading several documents , i am still confused whether to use Filestream or Varbinary for storing files?
my application need PDf files to be saved in database , size can be between 100 KB to 4 MB
I guess in order to use filestream we have to configure server to use that. while varbinary can work as it is.
what will be best way?Experts please comment.
Thanks.
|
|
|
|
|
|
We use filestream only to reduce the size of the database backup which is retained for some ridiculous number of years! documents are backed up seperately.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Hi Everyone,
I need a help in sql.
Below is the details:
from the front end, i will select few sql objects
for eg,
i am selecting
a) ProcedureName1, View_1 from Company_BAT and
b) GetEmpByProjID,GetAllProj, samplefunction from Company_DEV
here
ProcedureName1,GetEmpByProjID,GetAllProj are procedures.
View_1 is a view.
samplefunction is a function.
ProcedureName1 uses a) View_1 (View_1 uses Table_1)
b) Table_1
c) Table_2
View_1 uses a) Table_1
GetEmpByProjID uses a) Employee
GetAllProj uses a) Employee
b) Project
samplefunction a) it doesnt uses any objects
i need the output in the format as in the below screenshot in the below link:
http:
PLease advise, how to obtain result in this format.
|
|
|
|
|
hi,i have a problem!!!
How can I get the Sqllocaldb of database backup????
thankyou
|
|
|
|
|
|
Thanks for the reply .but I do not want backup in managment studio.
I want the backup (sqllocaldb) to do the coding in Visual 2012.
is It possible??/
|
|
|
|
|
i want to show report based on date condition and show it in gridview. It is working in my local machine but not working in server after hosting.
query is SELECT * FROM table WHERE DATE(colmn1) between 'startdate' and 'enddate'
What could be the problem.
If anybody knows please reply me.
Thanks in advance
|
|
|
|
|
ven753 wrote: What could be the problem.
It could be the format of the date. Why is there a DATE-function on colmn1? Is it a varchar?
Please don't crosspost. The question should not be in the .NET forum.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
i work in a broadcasting company i just need a suggestion to build a database for the company .The workflow is the data form nas,ftp,airbox, which will go in the database to validate the data and then it flow towards workstation is it possible ?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
