Hi ,
I've a DateTime field in a table and I want to separate it into two fields in an SQL Server 2005 view one for Date and the other for Time so What is the function I can use to do this process?
Best Regards,
Good morning.I am importing an XLS file into one of my tables. The fields are:Date Id Time IO12/22/2006 2 12:48:45 PM 912/22/2006 16 5:40:55 AM 112/22/2006 16 12:03:59 PM 2When I do the import, I get the following:Date Id Time IO12/22/2006 12:00:00AM 2 12/30/1899 12:48:45 PM 212/22/2006 12:00:00AM 16 12/30/1899 5:40:55 AM 112/22/2006 12:00:00AM 16 12/30/1899 12:03:59 PM 2Here are my doubts:1. Would it be better to combine the Date & Time fields into onecolumn? If so, how?2. What issues or problems might I have when I program SQL reports, ifI leave the fields as they are?Any comments or suggestions will be very much welcomed.Cheers mates.
View 2 Replies View RelatedGood morning.
I am importing an XLS file into one of my tables. The fields are:
Date Id Time IO
12/22/2006
2
12:48:45 PM
9
12/22/2006
16
5:40:55 AM
1
12/22/2006
16
12:03:59 PM
2
When I do the import, I get the following:
Date Id Time IO
12/22/2006 12:00:00AM 2 12/30/1899 12:48:45 PM 2
12/22/2006 12:00:00AM 16 12/30/1899 5:40:55 AM 1
12/22/2006 12:00:00AM 16 12/30/1899 12:03:59 PM 2
Here are my doubts:
1. Is it be better to combine the Date & Time fields into one column? Advantages/Disadvantages?
2. If I don't combine them, should I use varchar or datetime data type?
2. What issues or problems might I have when I program SQL reports, if I leave the fields as they are?
Any comments or suggestions will be very much welcomed.
Cheers mates.
Hello All,I've got a DATETIME field, and it includes hour:minutes:second data. I want to do selects where I can simply match on the month, day and year. For instance, something like this:SELECT * FROM QuizAttempts WHERE DateTimeTaken = '1/12/2006'And have it match anything that was taken that day, regardless of *when* it was taken. Any suggestions?Thanks! -Josh
View 2 Replies View RelatedHi,I use view to join difference table together for some function. However,when the "real" table fields changed (e.g. add/delete/change field). Theview table still use the "old fields".Therefore everytimes when I change the real table, I also needed open theview table and save it by SQL enterprise manager manually for update theview table field.Can we use a SQL command or other method to update it directly?Regards,Silas
View 4 Replies View RelatedI have a table with accountid and labtype
However inside of the labtype is data separated by semicolons like below
GIV;APS;LSL. What would be the best way to return this data as multiple rows such as
acctid lab
100 GIV
100 APS
100 LSL
Thee may be from 1-10 values in the labtype field
My group is working on a project for school and neither of us have much experience with SQL or ASP. We are pretty much just writing an SQL query to get the data in a certain date range and using ASP to write the tables with the data in it.
Our problem is the SQL database has separate fields for date and time. There is nothing we can do about this because the VB.NET program that populates the tables automatically does this. We can't figure out how to do the BETWEEN statement when they are separate fields. We can do it for a date range and it works fine. An example of what we want to do is show the data from 11:00 am on 04/01/2008 until 4:00pm 04/03/2008.
Does anyone have any idea what we can do? I really want to learn this but it's frustrating because we can't seem to get much help at school.
Can anyone please help me with the following:
I have a table which has fields called defid, datavalue, pid etc.
I have to create a new table which should have fields:
CustID, datavalue[SocSec]{note:this is the value in d.datavalue when defid is 1004), datavalue[FirstName]{note: this is the value in d.datavalue when defid is 1006}, datavalue[LastName]{note: this is the value in d.datavalue when defid is 1007}, [FullName] {note: this is the value I should get by joining the datavalues (lastname and firstname) when defifis 1006 and 1007) ie. ex: FirstNameLastName.
COULD ANYONE PLEASE HELP ME ON THIS. DATA QUERY is as follows:
Thank you in advance.
SELECT DISTINCT c.CustID, d.datavalue, f.name
FROM dbo.Customers c
INNER JOIN dbo.Date7 d ON d.pid = c.root
INNER JOIN dbo.fielddisc f ON f.defid = d.defid
WHERE c.separatedate BETWEEN @StartDate AND @EndDate
AND c.DateTab = 7 AND d.defid IN (1004, 1006, 1007, 1009) AND c.CustID = 10 AND c.root = 8472
ORDER BY c.root
i have two columns in a "release" table that i concatenate on my aspx page into one value for my dropdown list. for example, my database columns might look likeMajor: 3 | 3 | 4 | 4 | 5Minor: 1 | 2 | 1 | 2 | 1and my drop down list text like3.1 | 3.2 | 4.1 | 4.2 | 5.1my question comes when trying to take the drop down list's selected value and splitting it back into two fields so that i can use it for another query. how can i split my "major" and "minor" version numbers back up so that i can run a query similar to:SELECT * FROM [Version] WHERE (([Major] = @Major) AND ([Minor] = @Minor))but only having the one parameter of: <asp:ControlParameter ControlID="ddlVersion" Name="Version" PropertyName="Text" Type="String" /> where the selected value has a string of "<major>.<minor>" (where the numbers are separated by a period)?
View 6 Replies View RelatedI am using aloha POS and they have the date for every check in separate fields and now I want to calculate the total time for the checks but unable to get the how of it..
The date is DOB and it's datetime but I just need to extra the getdate() from it.The open time is OPENHOUR and OPENMINThe close time is CLOSEHOUR and CLOSEMIN
so basically the open time for the check will be the DATE FROM DOB + OPENHOUR + OPENMIN
And the close time will be DATE FROM DOB + CLOSEHOUR + CLOSEMIN
How can I get the total minutes for the check?
we have many max fields in our database .
A role in performance says : It's better to separate this fields to another FileGroup with separate file.
Is the result good for performance? and what are the risks?
I recieved a SQL Server table that was supposed to have just the firstname in a field, but actually has firstname and middle name.
Example David Michael
Carol Anne
Is there a way in a query to look for the blank space and separate the names?
Hi all, having a little problem with saving dates to sql databaseI've got the CreatedOn field in the table set to datetime type, but every time i try and run it i get an error kicked up Error "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated."I've tried researching it but not been able to find something similar. Heres the code: DateTime createOn = DateTime.Now;string sSQLStatement = "INSERT INTO Index (Name, Description, Creator,CreatedOn) values ('" + name + "','" + description + "','" + userName + "','" + createOn + "')"; Any help would be much appreciated
View 4 Replies View RelatedGood morning...
I begin with SQL, I would like to add a field that will be date like 21/01/2000.
Actually i find just "datetime" format but give me the format 21/01/2000 01:01:20.
How to do for having date and time in two different field.
Sorry for my english....
Cordially
A newbie
I know there has to be a way to do this, but I've gone brain dead. Thescenario..a varchar field in a table contains a date range (i.e. June 1,2004 - June 15, 2004 or September 1, 2004 - September 30, 2004 or...). Theusers have decided thats a bad way to do this (!) so they want to split thatfield into two new fields. Everything before the space/dash ( -) goes intoa 'FromDate' field, everything after the dash/space goes into the 'ToDate'field. I've played around with STRING commands, but haven't stumbled on ityet. Any help at all would be appreciated! DTS?
View 1 Replies View RelatedI receive a file that will have hyphens between data items such as
123-aed-edr-45r-ui9
1-ed3-45r-rrr-98u
I need to split the values to load into a table that will hold the 5 separate data itens. The fields will always have the hyphens but could be different lengths. Any idea on a best approach to split this in tsql
I embedded a SQL query in excel that gets some datetime fields like "TASK_FINISH_DATE" .
How can I convert a datetime field to a date field in SQL in a way that excel will recognize it as a date type and not a text type?
I tried:
CONVERT(varchar(8),TASK_FINISH_DATE ,3)
CONVERT(Date,TASK_FINISH_DATE ,3)
CAST(TASK_FINISH_DATE as date)
**all of the above returned text objectes in excel and not date objects.
I am trying to drag data from Informix to Sql Server. When I kick off the package
using an OLE DB Source and a SQL Server Destination, I get DT_DBDATE to DT_DBTIMESTAMP
errors on two fields from Informix which are date data ....no timestamp part
I tried a couple of things:
Created a view of the Informix table where I cast the date fields as datetime year to fraction(5), which failed.
Altered the view to convert the date fields to char(10) with the hopes that SQL Server would implicitly cast them
as datetime but it failed.
What options do I have that will work?
DECLARE @datetimeoffset datetimeoffset(3)
DECLARE @datetime datetime
SELECT @datetimeoffset = '2012-11-08T17:22:13.575+00:00'
SELECT @datetime = @datetimeoffset
SELECT @datetimeoffset AS '@datetimeoffset ', @datetime AS 'datetime'
__________________________________________________ ___________
Result of above SQL is
@datetimeoffset datetime
2012-11-08 17:22:13.575 +00:002012-11-08 17:22:13.577
__________________________________________________ ____________
The result should be '2012-11-08 17:22:13.575', why the milliseconds value is incorrect
Hey gang,
I've got a query and I'm really not sure how to get what I need. I've got a unix datasource that I've setup a linked server for on my SQL database so I'm using Select * From OpenQuery(DataSource, 'Query')
I am able to select all of the records from the first two tables that I need. The problem I'm having is the last step. I need a field in the select statement that is going to be a simple yes or no based off of if a customer number is present in a different table. The table that I need to look into can have up to 99 instances of the customer number. It's a "Note" table that stores a string, the customer number and the sequence number of the note. Obviously I don't want to do a straight join and query because I don't want to get 99 duplicates records in the query I'm already pulling.
Here's my current Query this works fine:
Select *From OpenQuery(UnixData, 'Select CPAREC.CustomerNo, CPBASC_All.CustorCompName, CPAREC.DateAdded, CPAREC.Terms, CPAREC.CreditLimit, CPAREC.PowerNum
From CPAREC Inner Join CPBASC_All on CPAREC.CustomerNo = CPBASC_All.CustomerNo
Where DateAdded >= #12/01/07# and DateAdded <= #12/31/07#')
What I need to add is one more column to the results of this query that will let me know if the Customer number is found in a "Notes" table. This table has 3 fields CustomerNo, SequenceNo, Note.
I don't want to join and select on customer number as the customer number maybe repeated as much as 99 times in the Notes table. I just need to know if a single instance of the customer number was found in that table so I can set a column in my select statement as NotesExist (Yes or No)
Any advice would be greatly appreciated.
Hi!
Need help with this one:
I have a column with a string composed by several data. After using REPLACE several times, I get something like the data below, which has (in most of cases) a value and a date.
378 9/05
388 9/05
4/05
1/06 606
1/06 646
76 5/05
100 1/05
118 8/05
129 8/05
9/05 342
05/3 123
1/07
4/06 164
The problem is that I need to get each value alone (to separate columns), in example:
Value Date
378 09/2005
388 09/2005
0 04/2005
...
606 01/2006
and so on...
In addittion you can see that sometimes the Value come first or alone, and sometimes the Date come first or alone.
I will appreciate any good ideas,
Thanks in advance,
Aldo.
I would like to compare 2 fields.. One of which is generated by the application upon insert. The other which is today's date.
In other words I want all records with today's date.
Problem is I get no results because I'm also apparently comparing the time.
Do I use a CONVERT function on my select & GETDATE outputs?
Thanks,
Kelly
I have a table with a DateTime field that has Dates in the format of mm/dd/yyyy I want to do a simple count on the table for the previous day but keep getting 0 for the results. Here is the script I've been trying.
SELECT COUNT(*)
FROM mytbl
WHERE Collected_Date = DateAdd (dd, -1, GetDate())
It appears to me that the time portion of the DateAdd function keeps the matches that I am looking for from happening. What am I missing or am I going about this all wrong?
Thanks for any help
I am trying to join two tables and the datetime fields need to be part of the join. They contain times in the fields too. I just need them to join on the date part though. Is there maybe a way to just return the mm/dd/yyyy format and join on that? Any and all help is appreciated.
Thanks,
Chris
I'm looking for an efficient t-sql script to loop through all usertables in a db and determine/print the value of each row having adatetime field (including cases where there are multiple datetimefields per row)Help greatly appreciated..
View 2 Replies View RelatedHi,
When I try to insert a new record into a table that has a datetime field that allows nulls, a default 01/01/1900 date is inserted instead of null. I recreated the table and set the datatype to smalldatetime and I still get the error. What have I missed?
Hi,We have a lame app that uses 2 datetime(8) fields, 1 stores the date, theother the time.example query:select aud_dt, aud_tmfrom ordersresults:aud_dt aud_tm2006-06-08 00:00:00.000 1900-01-01 12:32:26.287I'm trying to create a query that give me records from the current date inthe past hour.Here's a script that gives me todays date but I cannot figure out the time:select aud_dt, aud_tm, datediff(d,aud_dt,getdate()), datediff(mi, aud_tm,getdate())from orderswhere (datediff(d,aud_dt,getdate()) = 0)results:aud_dt aud_tmdatediff(0=today) timediff (since 1900-01-01)2006-06-08 00:00:00.000 1900-01-01 12:32:26.287 055978689I added this next part to the above query but it does not work since thedate/time is from 1900-01-01and (datediff(mi, aud_tm, getdate()) <= 60)Thanks for any help.
View 2 Replies View RelatedI have a requirement to display the elapsed time between two datetime fields. What i have been able to do is datediff the dates and get the elapsed time in minutes and then i am trying to format it as HH:MM.
For example, an activity started on Nov 1 2007 08:00 and finished Nov 1 23:59, the elapsed time should be displayed as 15:59.
The other example is if the activity spans over a couple of days, it should be say 49:03.
I put the following code in the format of the textbox, but it only seems to work when the length of the minutes is greater than 1.
=IIF(Len(CStr(Fix(ReportItems!textbox11.Value/60)))= 1,
"0" + CStr(Fix(ReportItems!textbox11.Value/60)),
CStr(Fix(ReportItems!textbox11.Value/60)))
+ ":"
+ IIF(Len(CStr(Fix(ReportItems!textbox11.Value Mod 60)))= 1,
"0" + CStr(Fix(ReportItems!textbox11.Value Mod 60)),
CStr(Fix(ReportItems!textbox11.Value Mod 60)))
Have i missed something really simple or what? Any help would be much appreciated.
Alternatively, if you have a better way of doing the whole thing would like to hear from you.
Thanks in advance.
Hello,I'm wondering if there is a way to concatenate two fields or a field and astring value in a single field in a view?Where in Access I might write;[field1] & " (m3)" as TotalVolumeis there a way to do this in an SQL Server View?Thanks!
View 2 Replies View RelatedHi all,I asked a similar question a few weeks ago but I didn't explain myself to well so I was hoping for some more input.I have created a webpage in Visual web developer that contains a gridview that points to an SQL table.All works well as far as searching the table and sorting. The SQL table itself is Droped ( deleted ) and then re-created every 1 minute from a query to a linked DB2 database so that we have a "live" table of our DB2 system but in SQL.....However,There is now a requirement to be able to ADD data to this SQL table. eg: a comments field.. from the website.My procedure for re-creating the SQL DB is now useless as any data added through the website would be lost when the table gets re-created.What is the best way for me to achieve a permanent table in SQL that gets updated from the Linked server DB2 but that I can also add comments from the front end (Website Gridview)..I'm assuming some sort of update query and a join to a seperate, permanent table that contains the comments?.Please help. Ray..
View 1 Replies View RelatedIn my database I have a table for Users, with an int primary key, and a table for Connections, with a combined primary key consisting of two UserID foreign keys. (the smallest first)
At the point I am stuck I have one UserID, lets call it current_user, and a column returned by a select statement consisting of UserIDs. Some of these IDs will likely be smaller than current_user, and some will likely be larger.
What i need to do is construct a view of two columns, combining each of the UserIDs in the column I have with current_user, with the smallest UserID of each pair residing in the first column, and the largest in the second column.
The point of this is to then SELECT the connections identified by the UserID pairs.
I suspect I could accomplish this if I could set individual fields in the a view, but I seem to have missed (or forgotten) that lecture. Anybody want to clue me in?
I'm using a view where I do a few things, including merge two short text fields into one. Just wanted to know if there was an alternative way of doing this:
Example:
SELECT first_name + ' ' + last_name AS full_name
FROM names
It works - but it seems rather blah. Is there something that'd be more "proper"?
Hi i need to concatenate a value into one field, i currently have a "view" in my sql which is retrieving values from a couple of tables including a junction table "tblUser_Constructor", someof these values are repeating at the moment, the only field changing is the constructor field, which i am trying to concatenate, for instance the following is happening;
User_ID UserName Constructor_ID Constructor
1 Jim 2 Keeley
1 Jim 5 Truro
below is my view;
CREATE VIEW dbo.vwUserASSELECT dbo.tblUser.User_ID, dbo.tblUser.Username, dbo.tblUser.User_Password, dbo.tblUser.User_Title, dbo.tblUser.User_Forename, dbo.tblUser.User_Surname, dbo.tblUser.User_Phone, dbo.tblUser.User_Mobile, dbo.tblUser.User_Email, dbo.tblUser.Access_Right_ID, dbo.tblUser.Tstamp, dbo.tblUser.Professional_Qualifications, dbo.tblUser.Organisation_Name, dbo.tblUser.Organisation_Address, dbo.tblUser.Is_MainContact, dbo.tblUser.LastLogin_DateTime, dbo.tblUser_Constructor.Constructor_ID, dbo.tblAccess_Right.AccessLevel AS [Group], dbo.tblCompany.Company_Name AS ConstructorFROM dbo.tblCompany RIGHT OUTER JOIN dbo.tblUser_Constructor ON dbo.tblCompany.Company_ID = dbo.tblUser_Constructor.Constructor_ID RIGHT OUTER JOIN dbo.tblUser LEFT OUTER JOIN dbo.tblAccess_Right ON dbo.tblUser.Access_Right_ID = dbo.tblAccess_Right.Access_Right_ID ON dbo.tblUser_Constructor.User_ID = dbo.tblUser.User_ID