| Author |
Topic |
|
Need_Help
Starting Member
5 Posts |
 Posted - 2010-03-24 : 09:47:32
|
| hi everyone,i'm new to this site so i don't really know if i'm posting on the right place. but since this is a sql site, i thought it might be better to ask my question here.ok, here goes..i'm having problems placing time values in my database table. when i place a time value, it always includes a date value. i've read a post stating that sql server 2000 does not include a time data type and only sql server 2008 has this data type. Also, i've seen a post stating that in order to have a time value without the date in my database table, i'm going to have to create a user defined data type or separate the time value from the date value and place them on different columns. According to the comments i've read about that article, they said that such a practice with obtaining time values is not very acceptable. i've tried using the method stated in that article but to no luck at all since i am new to using sql server. i've tried using sql server 2008 but i saw that the time data type is like this 'time(7)' and i don't know if i can use it since i need time values with AM and PM. and migrating from sql server 2000 to sql server 2008 doesn't seem to be working for me since i am using sql server 2000 on a windows server 2000 on a virtual pc and i have it all set up (connection to the host and everything) which i'm having trouble setting up in sql server 2008 with windows server 2008. that is why i decided to go back to my win server 2000 with a sql server 2000. does anyone have an idea as to what i can do about it? and how do i do it? i've been on this part of my project for days and i have a deadline to meet and now i'm really stuck. i'm sorry to be such trouble but i really really need help.. i badly need it....thanks in advance... |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-03-24 : 10:33:30
|
| SQL Server 2008 is the first version that has a separate data type for date and time. And if it's possible for you to install and use 2008 for this project of yours then that would be far better than the old 2000. The AM/PM issue your mentioning isn't a problem since this can be formatted using CONVERT: http://msdn.microsoft.com/en-us/library/ms187928.aspx. If you need to stick with 2000 you more or less have to hack it by either using a character datatype or by using datetime but with a static date (i.e. 1900-01-01) and then use convert to whatever format you need when displaying in front end. Or do presentation entirely in the front end...- LumbagoIf the facts don't fit the theory, change the facts. Albert Einstein |
 |
|
|
Need_Help
Starting Member
5 Posts |
Posted - 2010-03-25 : 13:46:33
|
| hi guys,thanks for the quick response..@webfredi do get the point of selecting the time portion from a datetime value but what i actually want is to store time only values in my database table and i need this time value to have AM/PM with it. my data would actually be coming from an excel file which i will load into my program and then get the data from it and store it in my database..@Lumbagoi did try using sql server 2008 and i saw the time data type but i'm having problems with connecting my virtual machine where my sql server 2000 is, to my host machine that is why i've resorted back to using sql server 2000. and i also can't do presentation of my data from the front end because that would mean i would have my database table filled with a columns filled with datetime values..anymore ideas?? thanks in advance... |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-03-25 : 14:31:44
|
quote:
Originally posted by Need_Help
hi guys,thanks for the quick response..@webfredi do get the point of selecting the time portion from a datetime value but what i actually want is to store time only values in my database table and i need this time value to have AM/PM with it. my data would actually be coming from an excel file which i will load into my program and then get the data from it and store it in my database..<snip>anymore ideas?? thanks in advance...
So are you saying that you already have an application consuming the data directly from a table and it needs to be in a specific format? Or are saying that you you are not sure how to store the time as a datetime is SQL 2000 and return it in a format that the consuming application can handle? Or something else?Perhaps a sample would help illustrate what you are trying accomplish?http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-03-25 : 14:39:02
|
Is it just a matter of formatting the select? From the example posted by Webfred:DECLARE @foo TABLE( dt DATETIME ) INSERT @foo(dt) VALUES('20011031') INSERT @foo(dt) VALUES('3:25 PM') SELECT RIGHT(CONVERT(VARCHAR(20), dt, 100), 7) FROM @foo --Results-------12:00AM 3:25PM |
|
|
|
Need_Help
Starting Member
5 Posts |
Posted - 2010-03-25 : 14:56:43
|
| hi Lamprey,thanks for the reply...this is my set up. i have a .net program from which i can open an excel file and then transfer only the data to my database table. initially my database table is blank. only the columns are set. what i did initially was transfer the data to my datagridview instead of transferring it to the database and this is because of the time values in the excel data. the time values in the excel data are in this format '03:00 PM' and its data type is 'time' which is set in the actual excel file. now, i need to transfer time values into my database table in sql server 2000 having such a format '03:00 PM'. and i have about 500 rows in the excel file.please let me know if i'm being unclear... thanks in advance.. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-03-25 : 15:35:23
|
| It sounds like you are getting hung up on the format of the data and not the data type of the data. If you REALLY want to store it as '03:00 PM' then, as Lumbago suggested, create that column as a VARCAHR or CHAR and roll with it.If you are unsure how to do a conversion or something we might be able to help. But, that sounds like more of a .NET issue than a SQL one, albeit a simple issue.If I've over simplified things, feel free to ask more questions. Sometimes the devil is in the details and there might be a detail one or all of us is missing. :) |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-03-26 : 03:32:44
|
| A general advice I can give you that I have experienced over time is that if you have problems importing data to sql server using the correct datatype, it will almost always be beneficial to import the data in to a staging table first with only varchar columns (or nvarchar). This import will never fail as long as the columns are wide enough, and after this import has been done you create a select statement that moves all data from the staging table to the production table with the proper formatting. It's far easier to handle data in a table than data in a file...In many cases it will actually also be more effective than using a format file etc. but that really depends on the data. For a project I did a while back this 2-step procedure with a staging table was roughly 20% faster than a bulk insert with a format file.- LumbagoIf the facts don't fit the theory, change the facts. Albert Einstein |
|
|
|
Need_Help
Starting Member
5 Posts |
Posted - 2010-03-28 : 14:07:33
|
hi everyone,sorry if it took me some time to reply. thanks for all your replies and thanks for your patience.@Lampreyyes, i did try to store it as CHAR but ended up including the date as well.@Lumbagothanks for the advice. i've also thought about creating a staging table and then passing it to a production table but thought that it would be taking up much more processing time. i initially did a bulk insert into my database table because there are almost 500 rows that i have to store in my table because looping through each row and then inserting them to the table doesn't seem to be such an acceptable practice considering the number of rows.i restored my table's column data type to CHAR and this will serve as my staging table. and i will do some formatting and then transfer it to another table. i think i can handle the formatting stuff. thanks for all the help guys. i really appreciate it! i'll let you know if anything goes wrong.  |
|
|
|
Need_Help
Starting Member
5 Posts |
Posted - 2010-03-28 : 14:12:32
|
| hi,by the way, how can i thank you enough? i'm i supposed to click something in order for you to gain points or something like that? please tell me how...thanks again! |
|
|
|
|
time values in sql server 2000