How To Split Datetime Column
I have column that hold datetime , i want to split the column into many columns ex:
column --> 01/01/2007 00:00:00
i want tp split to day month year hour minute second
View Complete Forum Thread with Replies
Related Forum Messages:
Conditional Split - DateTime Condition
Hi, I'm trying to check if a row was created yesterday? This does not seem to work? (MyId == "10") && DATEPART("dd",GETDATE()) == (DATEPART("dd",MyDateTimeColumn) - 1) Does anybody know how I can accomplish this? Many thanks.
View Replies !
Conditional Split - Compare DATETIME With Constant
Hi, I have to compare a DATETIME Field with '1/1/1900 12:00:00 AM". Which is default DATE TIME Value in SQL Server. I did compare like TRADEAGREEMENTFROMDATE != (DT_DBTIMESTAMP)(DATEPART("mm",(DT_DBTIMESTAMP)"1/1/1900 12:00:00 AM")) but (DT_DBTIMESTAMP)(DATEPART("mm",(DT_DBTIMESTAMP)"1/1/1900 12:00:00 AM")) returns "12/31/1800 12:00:00:AM" Thanks, Aravind
View Replies !
Millisecond Values Missing When Inserting Datetime Into Datetime Column Of Sql Server
Hi, I'm inserting a datetime values into sql server 2000 from c# SQL server table details Table nameate_test columnname datatype No int date_t DateTime C# coding SqlConnection connectionToDatabase = new SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=testdb;Integrated Security=SSPI"); connectionToDatabase.Open(); DataTable dt1 = new DataTable(); dt1.Columns.Add("no",typeof(System.Int16)); dt1.Columns.Add("date_t", typeof(System.DateTime)); DataRow dr = dt1.NewRow(); dr["no"] = 1; dr["date_t"] = DateTime.Now; dt1.Rows.Add(dr); for(int i=0;i<dt1.Rows.Count;i++) { string str=dt1.Rows["no"].ToString(); DateTime dt=(DateTime)dt1.Rows["date_t"]; string insertQuery = "insert into date_test values(" + str + ",'" + dt + "')"; SqlCommand cmd = new SqlCommand(insertQuery, connectionToDatabase); cmd.ExecuteNonQuery(); MessageBox.Show("saved"); } When I run the above code, data is inserted into the table The value in the date_t column is 2007-07-09 22:10:11 000.The milliseconds value is always 000 only.I need the millisecond values also in date_t column. Is there any conversion needed for millisecond values? thanks, Mani
View Replies !
Split A Column
Hi everybody Does any body know how to split a field in a table into two fields eg usermaster(table) userid(field) usermaster has 40 users with user id 1 to 40 i want to get data as userid userid 1 21 2 22 3 23 . . . . . . 20 40 Thanks you very much
View Replies !
Split A Column Into 2 Columns
Hi everyoneI guess this should be a simple question for the gurusI have a Data in a column which is to be places in 2 columns instead ofone. How do i go about doing it in MS SQL server? Could someone pleasehelp me. I could do it in access with an update query but things are alittle different in SQL server so I am a little lost.Eg.NameJohn?Doeto be split intoName LastNameJohn DoeThanks in advance.Prit
View Replies !
How To Split Three-value Column Into The Same Target?
Hi everyone, We've got a source file which owns three different values: 'A','B','M'. Where 'A' stands for "New Rows" and 'B' for "Delete rows" and 'M' for 'Update rows' Using Conditional Split task we can redirect each subset into a OLEDB Destination but we are wondering how can we do the same using only one OLEDB? We've got only one table. Thanks for your input and time,
View Replies !
Split Values From Within Column
I have been trying to separate firstname,last name,middle from name column Existing Format Column Name =FIRST,LAST M Desired First Last M I would llike to divide one column into three columns...How can i achieve it.. Please let me know
View Replies !
Split Column Into Severl Ones
Hello, I have a table which contains a column like that: Comment ----------------------------------------------------------------------- User: Toto Password: Toto-Toto I'd like to have in the same table: Comment User Password -------------------------------------------------------------------------------------------------------- User: Toto Password: Toto-Toto Toto Toto-Toto Do you have an idea of how to do it in SSIS? Thanks a lot for your help.
View Replies !
How To Split A Database Column ?
Hi, I have a column, for example Prod_ID count is 100 (contains Raw Matl & Finished Matl). I want to split this 2 columns as Raw Matl Finished Matl 60 40 Can anyone please help me how to do this in SQL Server. Thanks in Advance Rajesh
View Replies !
Split Data In Column
hai all, This is my first question to this forum. here is my situtation: I am into report testing I need to test a report for which i have write a query,iam using qery analyser for runing query Database : sql server tabel name :job_allocations column naME :technicain code Based on techincain code in joballocation tablei need to get technician cost from other table for the particular technician. Based on the technician code user chooses column will be updated if single data will be TC01 if more than one then data will be TC01:TC02:TC03 user can choose any number of techincian for a job MY problem is :How to split tha when there is multiple technician and calculate cost for the job Ineed it in single excecution query Table structure job_allocation table jobcardn_fk Technician_code jc01 TC01 jc02 Tco1:Tco2:Tc03...... I need it in jobcardno_fk TEchnician_code jco1 Tc01 jco2 Tc01 jco2 TC02 jc02 Tc03 TKs ands Regards Diwakar.R
View Replies !
Split One Column Into Multiple Columns
Hi all, I have a requirement like this , I have Address Column.It is containing data like Mr. K KK Tank Guntur Jal Bhavan, Univercity Road, Rajkot 9843563469 I have to split this into 3 more columns like(Address1,name,phoneno)-- Means i have 4 columns including Address Column.(Address,Address1,name,phoneno) Example: Address:Rajkot Address1:Univercity Road Name:Mr. K KK Tank Guntur Jal Bhavan PhoneNO:9843563469 How can i acheive this one with out data lose in Address Column. Thanks in advance.
View Replies !
Query Split Column In 2 Columns In SQL
I like to push 1 column into 2 different columns just to show it on the screen. So no import in another table ore something like that. I have a table like this: Select Name from Cars; Result: Col1 BMWMercedesFordAudi But i like to make a query so it is displayed like this: Col1 Col2 BMW FordMercedes Audi So i can bound a table directly to that column!Is this possible with SQL, and how can i build it.Thanks.
View Replies !
Split Data Into Two Column Table
Hello all, Little layout question. Assume my dataset returns the following data: A B C D E How can I present this data in a table (or list, or matrix) splitted into two columns: A B C D E Any idea will be very appreciated! Thanks a lot! TG
View Replies !
Query To Split A Database Column ?
How can i write a query to split a database column and shows 2 new columns. In my database column I have 2 mixing items and need to split out to 2 columns. Normally I have to write a query and change parameter and run another query. For example a database column with average number and range number. Thanks Daniel
View Replies !
How Do I Split A Column Result By A Nonalphanumeric Character?
I have a column that returns client numbers. The client numbers are 4-6 characters in length. A period (.) is added to the end of the client number, and then one last digit (1-4) is affixed at the end to denote a categorization. In SQL, I need to figure out how to divide these results into two columns, one for the client number and one for the categorization number. EG: client #4334.1 would become 4334 for client # and 1 for categorization number or Client #424561.3 would become 424561 for Client # and 3 for categorization number. I have to strip out the period in the process and leave myself with just the numeric characters divided into two columns. Ive been researching my brains out on string queries and substring queries and I can't figure out how to parse out the period and/or to have SQL understand that I need everything BEFORE the period for one column and everything AFTER the period for the second. Is it possible to do this? I really need help on this one. Thank you :)
View Replies !
Comparing A Column List Split To A Table.
Let me see if I can explain my situation clearly.I have a table with the columns:answer_id, question_id, member_id, answer- answer_id is the primary key for the table.- question_id relates to another table with questions for a user. Thetable holds the question and the possible choices in a varchar fieldseparated by a delimiter.- member_id is self-explanatory- answer is a varchar field of all the choices the user selected,separated by a delimiter.Here is my problem.I am trying to search all members that have answered, say, question_id= 2 where they selected 'brown' as one of their choices.i can do this if they selected ONLY that item, but not multiple items.The problem is this portionanswer in(select valu from dbo.iter_intlist.....I need this to be something like....function_to_return_all_separated_answers(answer) in(select valu from dbo.iter_intlistThe current way, it is only returning members that have an answer'Brown', not 'Brown, Blue' in their answer field. Make any sense? So,what I need to do is separate the list of answers and say :select member_id from profile_answers whereANY ANSWER in function_to_split(answer) MATCHES ANY OF THESE (selectvalu from dbo.iter_intlist...It seems I might have to join or something, I am just a little lostright now.Here is my proc.ALTER procedure search_detailed_get_ids@question_id as integer,@answers as varchar(8000),@member_ids ntextasdeclare @v as varchar(8000)--get the delimited string of all possible answersset @v = (select bind_data from profiles_questions where question_id =@question_id)--prepare it for the function only accepting 1 charset @v = replace(@v, '||', '|')--gimme all members that matchselect member_id from profiles_answers where question_id = @question_idand answer in(select valu from dbo.iter_intlist_to_table(@v, '|') where listpos in(select valu from dbo.iter_intlist_to_table(@answers, ',')))and member_id in (select valu from dbo.iter_intlist_to_table(@member_ids, ','))returngo
View Replies !
Split A Single Column Data In To 2 Columns
Hi This is probably a very basic question for most people in this group. How do i split the data in a column in to 2 columns? This can be done in access with an update query but in MS SQL server I am not sure. Here is an example of what i want to acheive FName John?Doe FName LName John Doe thanks for the help prit
View Replies !
Split Column Data Into Multiple Lines
Hi, I have a scenario, where I have a string column from database with value as "FTW*Christopher,Lawson|FTW*Bradley,James". In my report, I need to split this column at each " | " symbol and place each substring one below the other in one row of a report as shown below . "FTW*Christopher,Lawson FTW*Bradley,James" Please let me know how can I acheive this?
View Replies !
How To Split A Delimited Column Into Mulitple Rows In The Dataflow?
I'm sure there is probably a very easy solution that I am just not seeing or can't Google... I have a DataFlow that includes a column of Delimited values (i.e. Value1,Value2,etc..). As this DataFlow is populating a parent table, I need split the values into their own dataflow and populate a child table. I've tried a script transformation and couldn't figure out how to accept 1 delimited input row and output multiple rows after a split. Any ideas? TIA, Matthew
View Replies !
How Can I Split Fields And Depending One Column Decide The Foreing Key
I´m wondering how to solve the following scenario with SSIS I have a CITY table and a STATE table, I have to load a file with the information regarding to the CITY: the state table is like this: StateCode(PK) stateLegalCode stateName ============= ============== ========= 1 01 Florida the city table is like this: citycode(PK) cityLegalCode cityname StateCode(FK) ============ ============= ======== ============= 1 1001 Quakertown 1 the file has the following information cityLegalCode cityName ============= ======== 01-1001 Quakertown ... how can I load the file into CITY table: 1-) with the file's cityLegalCode I have to split the string and if the two initial digits are 01 the registry must have 1 in the StateCode(FK). how can I do something like that using SSIS??? thanks
View Replies !
Need To Find An Easy Way To Split A Column In Table Without Using Cursor Or Temp Tables
Hi , I have two tables within a SQL database. The 1st table has an identified column and column which lists one of more email identifers for a second table, e.g. ID Email -- ---------- 1 AS1 AS11 2 AS2 AS3 AS4 AS5 3 AS6 AS7 The second table has a column which has an email identifier and another column which lists one email address for that particular identifier, e.g. ID EmailAddress --- ------------------ AS1 abcstu@emc.com AS2 abcstu2@emc.com AS3 abcstu3@emc.com AS4 abcstu4@em.com AS5 abcstu5@emc.com AS6 abcstu6@emc.com AS7 abcstu7@emc.com AS11 abcstu8@emc.com I need to create a stored procedure or function that: 1. Selects an Email from the first table, based on a valid ID, 2. Splits the Email field of the first table (using the space separator) so that there is an array of Emails and then, 3. Selects the relevant EmailAddress value from the second table, based on a valid Email stored in the array Is there any way that this can be done directly within SQL Server using a stored procedure/function without having to use cursors? Many Thanks, probetatester@yahoo.com
View Replies !
Add Time To Datetime Value And Split Into Date And Time
Hi 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.
View Replies !
How To Group By A DateTime Column?
I have a column which Data Type is DateTime the value is looks like 2008-01-18 16:40:39.560if I want to group by this column only compare with yyyy/mm/dd , don't care the TimeHow to write a Sql Query to fit this request?the values in Columns like below2008-01-18 16:40:39.5602008-01-18 16:40:39.5602008-01-18 16:40:39.5602008-01-18 16:40:39.5602008-01-18 17:10:02.8572008-01-18 17:10:37.3732008-01-18 17:10:37.3732008-01-18 17:11:57.1872008-01-18 17:12:22.8102008-01-18 17:13:01.4502008-01-18 17:13:37.1532008-01-18 17:13:52.4502008-01-18 17:14:10.6402008-01-18 17:14:24.6532008-01-18 17:14:41.1532008-01-18 17:15:09.3572008-01-21 14:55:18.560 thank you for your posting .
View Replies !
Default Value In Datetime Column
hi, i was wondering how to set default value in the datetime column of the database so that it will enter current date and time if one is not provided when a row is populated. is there a store procedure to do this? or built-in function? mp
View Replies !
Problem With DateTime Column
Hi All,I faced this strange problem with sql server. I have datetime column and storing value from asp.net. If the user doesnt enter any date, then we dont want any value to be stored in the database. But when we checked SQL Server, it default takes this value "01/01/1900". When users clicks on edit button, this value is fetched from database and stored in front end. I dont want this value to be stored in database if i didnt provide value. But in 1 particular page, there are 5 different date columns. I am not sure about how to go ahead. I tried the following way, but again ended up with same problem.insert into tablename.......values (...., txtFromDate.text, txtToDate.text,....). Since value is not there, '' is sent to database and above said value is stored. i want NULL to be stored in teh database when user didnt specify any value.i didnt encounter this problem in Oracle.appreciate your reply.
View Replies !
Update A Datetime Column
I'm trying to update a datetime column from another datetime column. However, I just want the date transferred to the new column without the time. Any ideas? Thanks for your help.
View Replies !
Converting A Datetime Column
I've got have a populated table and I want to convert a datetime column so it lists the date only (without the time component) I tried to run this as a script, but returns an error: update <table> where <column>=convert(datetime,convert(char10),<column>,101)) When I run only this part, it does strip the date of the time component but it becomes a string, and I need this field stored as a datetime field: convert(char(10),hire_date,101) I'd appreciate any suggestions :)
View Replies !
CONVERT TO DATETIME COLUMN
HOW DO I CONVERT DATA AND TIME COLUMNS TO DATETIME COLUMN. I HAVE A REQUIREMENT TO FIND OUT THE MAX AND MIN OF DATE AND TIME COLUMNS WHICH ARE TWO SEPARATE COLUMNS ALL TOGETHER. I HAVE DATA IN COLUMN1 AND TIME IN COLUMN2 HAVE TO CONCATENATE BOTH THE COLUMNS TO GET THE MAX AND MIN TO DATE. EXAMPLE I TRIED TO DO : SELECT MIN(convert(datetime, VH_DATETIME,121), MAX(convert(datetime,VH_DATETIME, 121)) FROM (SELECT TOP 10 INPUT_DATE+INPUT_TIME AS VH_DATETIME FROM ADMINDB.dbo.SSIS_VISIT_HIST) VH Please help me in resolving this issue. Thank you
View Replies !
Format Datetime Column
Hello, I am wondering if someone could help me with formatting datatime column. Goal - keep column in datatime instead of convert to varchar Current - '2006-06-21 16:54:33.000' Wants - '2006-06-21 16:54:33' Any help is appreciated! -Lawrence Code Bits declare @time datetime set @time = '2006-06-21 16:54:33.000' select @time, convert(varchar(255), @time, 20)
View Replies !
DateTime Column Not Using Index
I'm having a hard time getting one of my tables to use a non clustered index that I have on a DateTime column. A sample version of the table is something like this CREATE TABLE Appointments ( ID INT NOT NULL, AppointmentDate DateTime NOT NULL ) with a clustered primary key on ID and a nonclustered key on AppointmentDate. This production table has over 1million rows and the problem I have is this: If I do a SELECT * FROM Appointments where AppointmentDate >= '20060701' AND AppointmentDate < '20060702' the Non clustered index on the AppointmentDate column works fine. i.e. I'm returning all appointments for the 1st of July. Now if I run the exact same query using datetime parameters, a Clustered index scan is performed instead of an index seek. DECLARE @AppDate DateTime SET @AppDate = '20060701' SELECT * FROM Appointments WHERE AppointmentDate >= @AppDate and AppointmentDate < DATEADD(day, 1, @AppDate) Any ideas why it would do this?
View Replies !
DateTime Format At Column
HI friends, Using SmallDateTime in SQL Table, how can I enforce M/DD/YY format on column of type smalldatetime in SQL ServerDB. What happening is that SQL convert this to YYYY-MM-DD, when my DTS package brings data to table? Note : output of DTS package is MM/DD/YY format. Thanks,
View Replies !
Max Date In Datetime Column?
Hello. In SQL server 2000 (or 2005) I have a need to query a table for rows with the max date value in a datetime column. Currently I coded the following to give me a specific date in the column: ...where R.start_time >= '02/15/2008' and R.start_time < DateAdd(day, 1, '02/15/2008') This gives me the rows with the exact date of 02/15/2008. So, if I don't want to hard-code the date or pass it as a parameter how can I always get the maximum date back from the query? Thanks. Walter
View Replies !
Interval On A Datetime Column
Hi, I have a table with 3 columns ( Id int, theValue float , aDate datetime). I want to select the number of rows (count) for which the interval of aDate is less than 7 seconds. Is there a way to achieve that in a single query. PS: This query will be perform on a dataset, that means that I can't use cursor. Thanks Sylvain
View Replies !
Milliseconds From DateTime Column
Hi, I am trying to access a date column up to millisecond precession. So I cast date to as follows: Code BlockCONVERT(varchar(23),CREATE_DATE,121) I get millisecond part as a result of query but it€™s €œ000€?.
View Replies !
Datetime Column With Milliseconds???
hi ppls.. we have sql server 2000 EM. we received daily xml files and we insert into our database.there is one column Date_T having data type datetime.till date we recieved this records from xml as '03/23/2004 12:23:34:956' but due to some duplicate isssue we now want to modified this column to recieve as milliseconds like '03/23/2004 12:23:34:956232' now my point is wheather sql server handle this kind of milliseconds..please help me out as early as possible.. T.I.A Papillon
View Replies !
Select Date From A Datetime Column
Well yeah as the topic says, this is my sql question right now;string date = "2006-11-12"; string sql = "SELECT * FROM spell_of_work WHERE date='{0}'",date);problem is that my date column in the table is datetime and not a date so.. error! i dont know if RLIKE or LIKE can help, wating for a reply!herman
View Replies !
Getting Last (newest) One Record (datetime Column Or Id)
Hello everybody,-------------------------------------------------CREATE TABLE [T1] ([IDX] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,[DateEvt] [datetime] NOT NULL,[Value] [varchar] (10) NOT NULL ,[DataX] [varchar] (10) NULL ,CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED([IDX]) WITH FILLFACTOR = 90 ON [PRIMARY]) ON [PRIMARY]GOinsert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:00:00','0000000001', 'AAAAAAAAAA')insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:00:01','0000000002', 'AAAAAAAAAA')insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:00:02','0000000003', 'AAAAAAAAAA')insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:01:00','0000000001', 'BBBBBBBBBB')insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:02:00','0000000001', 'CCCCCCCCCC')insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:03:00','0000000001', 'DDDDDDDDDD')GO-------------------------------------------------and the question is:In which fastes and best for the preformance way, get the last IDX ofspecified Value.I could do this like this:-------------------------------------------------declare @nIDX numericdeclare @sValue varchar(10)select top 1 @nIDX = IDX from T1where Value = @sValueorder by DateEVT desc-------------------------------------------------But I know, this is not fast (even if I have index on DateEVT field),and I'm quite sure, that there is better way to get this IDX.Anyway, this table can be big (like 20 milions records).I could take the max of IDX, but is it a sure way?Any help? Thanks in advanceMatik
View Replies !
Update Column With Type Datetime
Hi,I want to update a column typed datetime. My statement is:update 'tablename' set Datum_Ende = '2007-12-17 08:49:04.000' where'columnID_name' = 23250 and 'columndate_Name' = convert(datetime,'17.12.2007 08:08:04')This effects an error. The Server said, the dateTime value is out ofvalid Domain.I have tried various statements like this and i think, the problem isthe blank between date and time.Can someone help me?ThanksThomas
View Replies !
VB6 And MS-SQL 2000 Not Like '00:00:00' In Datetime Column Question
Dears I'd like to get the recordset of records that do NOT have the written value in datetime type column, for ex. select * from my_data where its_data not like '%00:00:00%' and its_data >= '2005-05-10' and its_data <='2005-05-12' or select * from my_data where its_data not like '00:00:00' and its_data >= '2005-05-10' and its_data <='2005-05-12' in such command I have all records also with (not desired) time 00:00:00 like 2005-05-11 00:00:00 Who knows the proper syntax? Rgds
View Replies !
Converting A Char Column To Datetime
Hello everyone, I have searched and seached for an answer to something that I know has to be simple but have been unsuccessful. I appreciate any help... I am trying to take a char (6) column named col001 and convert it to datetime. The column is in mmddyy format. I am using SQL 2000, but have available sql 7.0 servers if there is a difference. I expect that I have to write a cursor but have been unable to get the correct syntax. Thanks everyone
View Replies !
Format Datetime Type In Column
Hi Can you please help me on this matter please? I am trying to input UK(for example: 25/09/2007 ) format datetime in sql server. Is there any change that I can format my column to UK datetime. At the moment there is in US format(09/25/2007). I have a table, one of the column has type datetime. I use the insert command and the data i want to into is '25/09/2007' It come back with error that the date is out of range. I change that to 09/09/2007 then it work. The currently date and time format is MM/DD/YYYY. I don't know how can i define the format to be DD/MM/YYYY Thank you very much for your help i really do appreciate that. Best regards
View Replies !
Modify A Column From Nvarchar(50) To A DateTime
Hello, I have a column that is a currently set as nvarchar(50) and is called DateEmployed. There are over a hundred rows that contain dates which is in nvarchar format. This column now needs to be changed to a DateTime datatype. (Don't ask me it was not set to a dateTime when this was first designed - I wasn't here) However, I have to change this column to a DateTime without destroying the data. Is there any easy way to write some script or use studio management to change this. Currently the data is displayed like this in this column dd/MM/yyyy i.e. 25/8/2007. The method I am using to try and change this is by going to studio management clicking modify on the column and changing the datatype from a nvarchar(50) to a DateTime. I get this following error message: - Unable to modify table. Arithmetic overflow error converting expression to data type datetime. The statement has been terminated. Any suggestions would be most grateful, Thanks, Steve
View Replies !
How To Install A Null Value Into A DateTime Column Via A TableAdapter?
Hi,I have a table adapter that I am using to call a stored procedure on a SQL server database to insert a record.Everything works ok, except I cannot figure out how to pass a null value to a DateTime field. In my SQL server database, the column allow nulls. In my typed dataset, the column is set to allow nulls.When I try to run the insert procedure with a null value: ListTableAdapter da = new ListTableAdapter(); da.InsertList(System.Data.SqlTypes.SqlDateTime.Null, Name); I get this error: Error 14 Argument '1': cannot convert from 'System.Data.SqlTypes.SqlDateTime' to 'System.DateTime?I've also tried passing it DBNull.Value, but I get an exception with that too.Can anyone steer me in the right direction with this?
View Replies !
Format Of DateTime Column In SELECT Query
I am using SQL2005 and ASP.NET 2.0 I have one column in database called DateTime and it is defined like type datetime.It is formated like: day.month.year hour:minutes:seconds My question is: I want to get date from Column DateTime in format day.month.year in SELECT query, not in stored procedure. thanks
View Replies !
Extract The Value Of A Datetime Datatype From A DataSet Column
Hello, I'm having trouble extracting the datetime value of a datacolumn in order to compare it's value to another datetime. The error I get is "unable to cast object of type System.Data.DataColumn to type System.DateTime. I'm sure I'm just missing a step or a cast, but nothing has worked. I hope someone can help! Here's the code: protected void ddlRooms_SelectedIndexChanged(object sender, EventArgs e) { SqlCommand cmd; SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EventBookingConnectionString"].ConnectionString); SqlDataAdapter da; DataSet ds; try { cmd = conn.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "SELECT Events.ID as 'Event ID', Name as 'Event Name', Rooms.Room as 'Event Room', AttendeeList as 'Attendee List', EventDate as 'Event Date' from Events INNER JOIN Rooms on Events.Room=Rooms.ID"; ds = new DataSet(); da = new SqlDataAdapter(); da.SelectCommand = cmd; da.Fill(ds, "BookedRooms"); if (ds.Tables["BookedRooms"].Columns["Event Room"] != null) { foreach (DataRow row in ds.Tables[0].Rows) { string room = (string)row["Event Room"]; if (ddlRooms.SelectedItem.ToString() == room) { System.DateTime date = (System.DateTime)ds.Tables[0].Columns["Event Date"]; //HERE'S WHERE THE ERROR OCCURS if (Calendar1.SelectedDate == date) { lblBookedRooms.Visible = true; lblBookedRooms.Text = "I'm sorry, that room is booked for that day.<br />Please choose another day or a different room."; } } } } } catch (Exception ex) { lblResults.Text = ex.Message; } } Thanks in advance to anyone willing to help out! Monster
View Replies !
Insert Statement For Datetime Column Fails
I ve a simple table with a column of type datetime. I ve successfully inserted the following values in it, 2006-09-13 18:00:10 2006-09-14 18:00:10 2006-09-15 18:00:10 however, it fails when i try to insert the value 0000-00-00 00:00:00. ie., the following insert statement fails INSERT INTO TEST VALUES('0000-00-00 00:00:00') The error thrown is, Server Msg 242, Level 16, State 3, Line 1 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.
View Replies !
|
TRACKER
