Is there a way to extract the time from a field like this
2005-11-14-14:30:00:000
I just want to use the 14:30 out of that field but cant seem to find the right way to do it. I can use datename and get the hour but i need it to the min.
Any help on extracting the time part from the datetime field in SQLdatabase.even though i had entered only the time part in the database when iextract the field it gives me only the date part. i’m using Vb.netdatagrid as a front end.any assistance appreciated!! :?:--Posted using the http://www.dbforumz.com interface, at author's requestArticles individually checked for conformance to usenet standardsTopic URL: http://www.dbforumz.com/General-Dis...pict254266.htmlVisit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=877989
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.
I'm having a bit of trouble with dates/times. All I want to do is extract the contents of a table in an old Informix database and load it in to table in a SQL 2005 database.
The problem is that when I try to extract from the source table, I get the following error:
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E07 Description: "Error converting data type DBTYPE_DBTIMESTAMP to datetime.".Error: 0xC004701A at Dump Dacom Tables, DTS.Pipeline: component "mac_header" (181) failed the pre-execute phase and returned error code 0xC0202009.
It would have been nice if it could tell me what columns it was having trouble with. Anyway, I figured it out. There are two columns called time_open and time_close that contain times but no dates. This is how the source columns are configured:
time_open datetime hour to minutetime_close datetime hour to minute
This is a really old database. We started using it about 14 years ago. I can get an ODBC connection to it fine with any application except SQL Server. So, I have to do an OPENROWSET to get access to the data.
SELECT * FROM OPENROWSET('MSDASQL', 'Connection String Stuff', 'select * from dacom.mac_header') Question 1: Why can't I get an ODBC connection to this Informix 5.10 database?
Question 2: How can I extract tables with this sort of data in it? I'm happy to store it as a normal date/time field in the destination SQL 2005 database, but I can't convert something that I can't extract.
I'm looking for a way of taking a query which returns a set of date time fields (probable maximum of 20 rows) and looping through each value to see if it exists in a separate table.
E.g.
Query 1
Select ID, Person, ProposedEvent, DayField, TimeField from MyOptions where person = 'me'
Table
Select Person, ExistingEvent, DayField, TimeField from MyTimetable where person ='me'
Loop through Query 1 and if it finds ANY matching Dayfield AND Timefield in Query/Table 2, return the ProposedEvent (just as a message, the loop could stop there), if no match a message saying all is fine can proceed to process form blah blah.
I'm essentially wanting somebody to select a bunch of events in a form, query 1 then finds all the days and times those events happen and check that none of them exist in the MyTimetable table.
I am trying to load previous days data at 3 am via a SSIS job.
The Date variable is initiated as DATEADD("dd",-1, GETDATE()) in the for loop.
Now, as this job runs at 3 am, and I set the variable as GETDATE() - 1, it excluded the data from 12 am to 3 am in the resultset as Date is set as YYYY-MM-DD 03:00:00:000 I need this to be set as YYYY-MM-DD 00:00:00:000
Hi there. I'm trying to extract data from my SQL server & everything in the script I've got is working (extracting correct data) except for one field - which is for the most part it's off by +2 days (on a few occasions - I see it off by just +1 day or even +3, but it's usually the +2 days).
I'm told that it's due to the conversion formula - but - since SQL is not my native language, I'm at a bit of a loss.
The DB table has the date field stored as a type: CHAR (as opposed to 'DATE') Can anyone out there help?
A server with SQL 2005 sp2, Reporting Services and Sharepoint services (ver 3.0) (in integrated mode) gives an odd error. When viewing a Reporting Services report with a Date Time Picker, the date chosen is wrong. The preferred setting is Danish with the date format dd-mm-yyyy. The date picker shows the months in Danish but when selecting a date, and clicking on the Apply-button, the date reformats to US (mm-dd-yyyy).
Example: When choosing 5th of September 2007 and clicking apply, it shows in the picker, 9th of May 2007. When choosing 26th of September 2007 and clicking apply, it shows, again in US format, the RIGHT date but adds a timestamp €œ12:00 AM€? in the end, making further enquiries to fail.
The report itself receives the right date and shows correctly. The only case it fails is, when the time stamp appears.
The server is a 32-bit one with 4 GB RAM. A testserver with identical collation on the Reportserver database cannot recreate the error. The site containing the reports has been set to Danish in the regional settings. To Reinstall is not an option.
The test report has no database connection whatsoever.
When setting the site to US, the timestamp won€™t appear at all.
The server has been restarted and the installation procedure was of the simple kind. No special tweaks at all.
I have passed createdDate from UI to Stored procedure.createdDate field declared with DateTime.it is having value 2014-07-01.I need to fetch records from the database based upon the created field.but Create_TM in database having value Date with timestamp.so how would i change the createdfield in stored procedure.
ALTER PROCEDURE [dbo].[ByDateRange]
@Feed VARCHAR(50),
@CreatedDate DATETIME
select * from Date_table where Create_TM = @CreatedDate
I have a table named "shift" and I need to setup my query to return only data where the field "startime" = today. The problem I am running into is the starttime field it laid out like "2005-12-29 14:00:00" with different time values. I need to ruturn everything that has todays date regardless of the time value. I tried using GetDate() but that is returning data for other days as well or just data before or after the current time. Does anyone have any suggestions? This is driving me crazy! Thanks, Garrett
We have a bunch of Audit tables that contain almost exact copies of the operations tables. The audit tables also include:
AuditID - the audit action (insert, modify - old, modify - new, deleted) AuditDate - date and time of action AuditUser - User who did it...
At the end of the day I need to know for any given record what it looked like at the beginning of the day and what it looks like at the end of the day. There could have been numerous changes to the record throughout the day, those records I am not interested in. Only the first record and the last record of a give day.
I am going to be doing a lot of MIN(AuditDate) and MAX(AuditDATE) and .. WHERE AuditDate BETWEEN '10/1/2007 00:00:00' AND '10/1/2007 11:59:59' ...
Question: Whats better for performance:
1. Separating out the date and time and doing a clusterd index on the date.
2. Keeping date and time in the same column and just use a normal index.
Hi,I need a way of changing the following SQL statement so that the dates are without the hh:mm:ss tt:"Select DISTINCT([StartDate]) From [Events]"How can this be done?Thanks,Curt.
I have a table in which a date value is stored as varchar.some of these values are stored ina dd/mm/yyyy format and other values are stored in a yyyy-mm-dd format..Now I wish to retrieve some data by querying between two dates. However I need to convert the varchar date value to datetime in order to do this but since the date value is in two different formats, the following doesn't work.
select date_value from my_table where CONVERT(DATETIME, date_value, 103) between @date1 and @date2
How can you convert the date value to datetime when its stored in mutiple formats. I can't change the table itself as I dont have admin privelages.
I am using the calender parameter and I need to convert my data date format to the one that matched that is returned on selecting a date from this calender. Can you show me what this format is.
how can I convert my existing date format to this format. The existing date format is 2007-07-26 21:27:13.000
How can I obtain just the time portion from a date/time column? My data contains "2008-05-19 09:30:00.000" Actually, all I want/need is the hh:mm part of it.
I have a table that's of type date/time (i.e. 01/01/1900 00:00:00).What I want is to do the following:Say you have these records:person | date-time-------+---------------------------jim | 06/02/2004 00:05:52jim | 06/02/2004 05:06:21jim | 06/02/2004 05:46:21jim | 06/15/2004 11:26:21jim | 06/15/2004 11:35:21dave | 06/04/2004 09:35:21dave | 06/04/2004 11:05:21dave | 06/06/2004 10:34:21dave | 06/08/2004 11:37:21I'd like the results to count how many days and returnperson | days-------+-------jim | 2dave | 3How would I do this?--[ Sugapablo ][ http://www.sugapablo.com <--music ][ http://www.sugapablo.net <--personal ][ Join Bytes! <--jabber IM ]
I have an SSIS package that moves data from SQL Server to an legacy Access database. In SQL Server, there is a date/time column that I need to split into a separate date column and time column in the access database. Initially I just created a derrived column for the time and set the expression equal to the source date/time column from SQL Server. Unfortunately, that just makes the date column and time column the same having the full date/time.
What expression can I use during a derrived column transformation to assign just the date to a derrived column and just the time to another derrived column?
I would like to know how to do a select on a datetime field that will return to me only the Date but not the time. Please help out. Thanks. I ran through all the built in date functions and couldn't find anything for this task. I still can't fathom why there isn't a date function for this simple but a must have option. blumonde
Hi I have created a simple SQL database with four columns using Microsoft SQL Server Mangement Studio Express - (Name(varchar), Email (varchar), Comment (varchar), Datetime). The idea is for this database to hold feedback and comments from my website by connecting a form view with insert capabilities to this database. How can I configure the Datetime column in the database to automaticaly input the time and date from when the user posted the comment?