DATE/TIME CODE
Sep 24, 2001BREAK THE DATE TIME FUNCTION DOWN INTO MINUTES...HELP!
View 1 Replies
ADVERTISEMENT
Conversion Of Oracle Date Time To Sql Server Date Time In SSIS
Jun 30, 2007This 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
SQL Server 2008 :: Loop Through Date Time Records To Find A Match From Multiple Other Date Time Records?
Aug 5, 2015I'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.
Integration Services :: Exclude Time In Date Time Variable In SSIS For Loop?
Oct 22, 2015I 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
How can i do this?
How To Convert A Date (date/time) To A Julian Date
Jun 13, 2002In SQL Server 2000:
How do I convert a Julian date to a Gregorian date?
How do I convert a Gregorian date to Julian?
Examples please.
Many thanks in advance.
Gary Andrews
Date Function - Conversion Failed When Converting Date And / Or Time From Character String
Mar 18, 2014I 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]
Transact SQL :: Due Date - Conversion Failed When Converting Date And / Or Time From Character String
Nov 16, 2015SELECT * ,[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?
How Do I Convert A Unix Date/Time Field To A Date When The The SQL DB Stores That Data As Char 11?
Nov 13, 2007Hi 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?
Please advise. Thanks.
Best.
K7
Sharepoint Integration With Erroneous Date Format In Date Time Picker
Sep 5, 2007Dear Expert!
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 wont appear at all.
The server has been restarted and the installation procedure was of the simple kind. No special tweaks at all.
Any advice would be greatly appreciated.
Kind Regards
Johan Rastenberger
How To Find Out Date/time When Row Was Updated Last Time
Jan 15, 2002Hello,
we need to track date/time of last update for each record in a table.
As we understand it, we can't use field type Timestamp as this type does
not use dates/times.
Is there any SQL function available which we can bind to a column or
do we really have to use triggers?
Greetings from Mannheim, Germany
Ricardo
How To Find Out Date/time When Row Was Updated Last Time
Jan 15, 2002Hello,
we need to track date/time of last update for each record in a table.
As we understand it, we can't use field type Timestamp as this type does
not use dates/times.
Is there any SQL function available which we can bind to a column or
do we really have to use triggers?
Greetings from Mannheim, Germany
Ricardo
Update Time In Date-time Field?
Nov 11, 2013I want to update the time in a datetime field with the current time.
Fields current value is:
2013-11-11 00:00:00.000
I want to insert this into another table and when I do I want to grab the current time and update that field.
field name: picked_dt
Table: oeordlin
or is there a way through sql to update the time when the picked_dt is updated?
Add Time To Datetime Value And Split Into Date And Time
Jun 12, 2007Hi
i have the following situation. in my database i have a datetime field (dd/mm/yy hh:mms) and i also have a field timezone.
the timezone field has values in minutes that i should add to my datetime field so i have the actual time.
afterwards i split the datetime into date and time.
the last part i can accomplish (CONVERT (varchar, datetime, 103) as DATEVALUE and CONVERT (varchar, DATETIME, 108) as TIMEVALUE).
could anybody tell me how i can add the timezone value (in minutes) to my datetime value ?
i do all the calculations in my datasource (sql).
Thanks
V.
Display Only The Date Part Of A Date And Time Field?
Mar 16, 2014I want to display only the date part of a date field which contains both date & time information.
For example I have the value '2013-11-14 00:00:00.000' in my result set, and ideally I would like to show only '2013-11-14'.
I have looked up the datepart() command, however I can't work out how to return all parts of the date, rather than just the year, month, or day.
How To Convert Date-field To Date With Time Zone
Jul 4, 2014I 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
Problem With Current Date For Date & Time Field
Dec 29, 2005I 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
View 7 Replies View RelatedPutting Date Or Date && Time In A Column Thats Going To Be Heavily Used?
Oct 4, 2007Hello,
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.
3. Better ideas?
Thanks,
Bradley
Time-series SQL Code.
Jul 23, 2005I am trying to write a stored proc the calculates a moving average overthree periods. In the following example, I need to stratify the data bypersonID and RecordID in the #Temp table, but I am not sure how to doit. Right now I am restricting the data I use to build my time series bypersonID and I get the results I want *by PersonID*. If I can figure outhow stratify by personID so I don't have to use this restriction, I'msure I can extend it to the RecordID.Create Table #Temp(tmpID int identity,DetailID int,RecordID int,AdminDate Datetime,AdminTime datetime,Status tinyint,--decimal(9,2),Location varchar(100),PersonID char(9),PatientName varchar(100),DOB Datetime,Drug varchar(100),Sort varchar(10))--populate with data by personIDinsert into#Temp(DetailID,RecordID,AdminDate,AdminTime,Status ,Location,PersonID,PatientName,DOB,Drug,Sort)Select MD.PatMedOrderDetailID, MD.PatMedOrderID, M.Date as AdminDate,Case M.Time When 'A' then '8:00:00 AM' When 'N' then '12:00:00 AM' When'P' then '4:00:00 AM'When 'H' then '8:00:00 PM' else M.Time End as Admintime,100*M.Status, P.Location,P.PersonID, P.Name as PatientName, P.DOB,D.GenericName + ' (' + D.TradeName + ') ' +D.Strength,Left(P.Location,3)From PatMedOrderDetail MD Inner Join PatMedOrder MO on MD.PatMedOrderID= MO.PatMedOrderIDinner Join PatMedPass M on MD.PatMedOrderDetailID =M.PatMedOrderDetailIDinner join Patient P on M.PersonID = P.PersonIDinner join Drugs D on MO.DrugID = D.DrugIDWhere P.PersonID = '000126230'Order by P.PersonID,MD.patMedorderID, M.Date, M.TimeSelect * from #Temp -- to view entire set--returns relevant rowsSelect Derived.RefusalRate,T.* from #Temp T inner join(select t1.tmpID, avg(t2.Status) as RefusalRatefrom #Temp t1 cross join #Temp t2WHERE t1.tmpID>=3 AND t1.tmpID BETWEEN t2.tmpID AND t2.tmpID+2group by T1.tmpIDhaving avg(t2.Status)< 100) as Derived on T.tmpID = Derived.tmpIDDrop Table #Temp*** Sent via Developersdex http://www.developersdex.com ***
View 1 Replies View RelatedFormatting Date SQL Date To Remove Time
May 2, 2008Hi,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.
View 4 Replies View RelatedHow To Convert Date String To Date Time
Jan 8, 2013I 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.
Date Time Format For Date Parameter
Jan 24, 2008
Hello,
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
thank you
Kiran
Getting Time From Date/time Column
May 16, 2008How 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.
Thanks,
Walt
How To Modify Schema At Run-time - My Code Or SQL Server?
Mar 27, 2007Hi Smart Folks,
My SQL Server-based app will require generating a few tables at run-time, and, possibly, a new small database. Again, this is at run-time. Any advice on this? Should I build the functionality from the app, having my code generate all of the commands on the db to create what I need, or should I rely on SQL Server functionality (Scripts? SPs?) to do so. I am sure there is trade-off in one appraoch versus the other - I could use a tour of the trade-offs.
Thanks all!
DF
Date Part Of Date Time
May 5, 2004SELECT ltrim(str(datepart(yyyy,getdate()))) +'-'+
replicate('0',2-len(ltrim(str(datepart(mm,getdate())))))+ltrim(str (datepart(mm,getdate())))+'-' +replicate('0',2-len(ltrim(str(datepart(dd,getdate())))))
+ltrim(str(datepart(dd,getdate())))
This is how i am getting datepart of datetime.Is there any other way to get the date and also time seperately..
Thanks.
Group By Date But Not Date Time
Feb 28, 2008i have a query
select mydate,lastname from users
group by mydate,lastname
now the only thing is mydate is a datetime fields but i want the results to group by just the date for each lastname entered.
How do i do this?
Converting Date/time To Just Date?
Jul 20, 2005I 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 ]
View 1 Replies View RelatedSplitting SQL Server Date/Time Column Into Access Date Column And Access Time Column
Jan 24, 2008I 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?
Thanks,
Steve
Finding Last Day Date Code
Aug 1, 2014I wanted to determine the date of the last Saturday with respect to the current date so that I could create a temp table with date ranges numbered 1-4 e.g. up to that date, post link below: URL....I now want to make this function that any time I pass it a day name (mon-sun) it will calculate the date of the previous (mon-sun) with respect to the current day. This date will then be the last date in my range.
So for example I run the function and pass it 'Friday' then I want last date in my range to be the 25th of July (today being Friday so need last Friday), I pass it Thursday I want it to give me the 31st of July etc. I need last date of that date but if I call Friday and today is Friday then need last friday; I think this is the trickier part of it.
This calculation I would image would need to use a combination of datediff dateadd etc. but really don't know how to do this dynamically.Currently I have a function that I pass the last date for the date range I want for my weeks range, I also give it the numbere of weeks I want in my range and also pass the date from the query that I want to know what week range it is in.
ALTER FUNCTION [dbo].[daterangeweeknumber]
(
-- Add the parameters for the function here
@refDate datetime,
@Weeks INT,
@lastdate datetime
]code]....
But now I want just to be able to pass it the day name - in this instance would be 'Wednesday' and want it to return date ranges with last date as Wed the 30th of July. Reason I want to use day name is that people will be running these reports on a certain day - for instance on Wednesday and last day to be Monday - and this will mean they do not have to keep changing the date in the call to function.
Trouble With Previous Date Code
Oct 24, 2007I am new to SQL; the following code is supposed find comments that are not null on one day before the studydate.
SELECTp.PatientID,P.LastName,P.FirstName,P.BirthTime,s.StudyDate,s.Modality,s.Comments
FROMStudies s INNER JOIN Patients p
ON s.PatientID = p.PatientID
WHEREs.Comments IS NOT NULL
AND s.StudyDate >= CONVERT(char,DATEADD(day, -12, GETDATE()),101)
AND s.StudyDate < CONVERT(char,DATEADD(day, -11, GETDATE()),101)
Is there a problem with this code? I expect there is because when I ran the query it didn't return any results even though I am positive there are comments in the database one day before the studydates.
Any help would be greatly appreciated
Thanks a ton,
Regards
-avery
Problem In C# Code Behind And With SQL String Date And The Between Statement
May 13, 2008HiI have a problem to search between two dates, if I use Convert(varchar(10),ContactCreateDate,105) in a single textbow and for a single search date it is okay, but when I use the Convert(varchar(10),ContactCreateDate,105) against two textboxes it did't function.Can anybody help me ?MY codeif (TBContactSearchDateStart.Text.Length > 0 && TBContactSearchDateEnd.Text.Length > 0)
{
strWhere += " AND Convert(varchar(10),ContactCreateDate,105) BETWEEN '%" + TBContactSearchDateStart.Text + "%' AND '%" + TBContactSearchDateEnd.Text + "%'";
Data Types For Zip Code And Date (mm/dd/yy) In Sql Database
May 19, 2008Hello All,i have a sql database with a table as follows. CustID (PK, Varchar(50) not null )Address ( VarChar(max), null)City ( VarChar(max), null)State( VarChar(max), null)Zip ( VarChar(5), null) ----------------------------- this is not working cuts off the leading zero like ( 01234) is shown as 1234DateShipped( datetime, null) --------------------- this display 05/18/2008 12:00AM. i just want to display (05/18/08).how can we do the above two. any suggestions please. i tried smalldatetime no luck . i appreciate it.Thanks.
View 3 Replies View RelatedDate But Not Time
Jul 13, 2006I 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
SQL TIME AND DATE
Apr 3, 2008Hi
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?
Thanks