| Author |
Topic |
|
Evolved
Starting Member
6 Posts |
 Posted - 2009-03-19 : 07:54:42
|
| I read a few topics about this, and one looked really useful:[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=22339[/url]I use the SQL code to create the function, but when I try to use the function it says it does not exist.I have four columns in a table, the Day, the Month, the Year and the Time.I need to join them together to create a datetime.In Access the SQL code produced is very similar to Excel's 'DateSerial' function, but Dateserial does not work within my SQL database - only in Access.This MUST be really easy to do, far easier than the complex examples listed in the thread I linked. |
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-03-19 : 08:01:39
|
| is this u want declare @date varchar(32), @month varchar(32), @year varchar(32), @time varchar(32)select @date = 21, @month = 2 , @year = 2009 , @time ='12:45'select cast(@month +'-'+ @date+'-'+@year +' '+ @time as datetime) |
 |
|
|
Evolved
Starting Member
6 Posts |
Posted - 2009-03-19 : 08:08:50
|
| Thanks, but that dopesn't work. After I change that code to point towards my data, it gives the error:Msg 242, Level 16, State 3, Line 5The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.It DOES work when you use numbers as the date as your code does, but my numbers are Varchar(255)s, would that be what causes the problem? |
|
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-03-19 : 08:16:39
|
| u should concatnate in yyyymmdd format or mmddyyyy format |
|
|
|
Evolved
Starting Member
6 Posts |
Posted - 2009-03-19 : 08:20:01
|
| I'm not sure what you are trying to get me to do...If I do that I'll have an even less useful string of characters. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-19 : 12:05:03
|
quote:
Originally posted by Evolved
I read a few topics about this, and one looked really useful:[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=22339[/url]I use the SQL code to create the function, but when I try to use the function it says it does not exist.I have four columns in a table, the Day, the Month, the Year and the Time.I need to join them together to create a datetime.In Access the SQL code produced is very similar to Excel's 'DateSerial' function, but Dateserial does not work within my SQL database - only in Access.This MUST be really easy to do, far easier than the complex examples listed in the thread I linked.
something like SELECT DATEADD(ss,Time,DATEADD(dd,Day-1,DATEADD(mm,Month-1,DATEADD(yy,Year-1900,0)))) AS Date FROM YourTable assuming your Time field is int value giving number of seconds |
|
|
|
Evolved
Starting Member
6 Posts |
Posted - 2009-03-19 : 12:22:03
|
| Thanks for trying to help, but how would your query be able to know what columns in my table to look at?I can get the date to display when I use numbers in a query, but I cant get the date to display based on what is actually in my table.I wonder if null values cause the error, it doesn't error as if they do, but its the only thing I can think of, everything else looks correct.I just wish there was more information available about how to write these damned things. The online help for SQL by microsoft is incredibly poor. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-19 : 12:43:31
|
| then post how data exists in your table, give some sampledata and value you want out of the |
|
|
|
Evolved
Starting Member
6 Posts |
Posted - 2009-03-20 : 05:33:09
|
| My data is in varchar (255) format like I said earlier, in columns of the day, the month, the year and the time. I want to make a datetime value of dd/mm/yyyy hh:mm.I can't understand why Access is able to do this manipulation INCREDIBLY easily, but it is apparently impossible to do in SQL. |
|
|
|
tosscrosby
Aged Yak Warrior
676 Posts |
Posted - 2009-03-20 : 09:35:30
|
| If Access can do it, I'm certain SQL can. Show some of the actual data that you're trying to manipulate. One question, why is your date/time information stored that way in the first place?Terry-- Procrastinate now! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-20 : 09:54:34
|
quote:
Originally posted by Evolved
My data is in varchar (255) format like I said earlier, in columns of the day, the month, the year and the time. I want to make a datetime value of dd/mm/yyyy hh:mm.I can't understand why Access is able to do this manipulation INCREDIBLY easily, but it is apparently impossible to do in SQL.
show some sample please in following formathttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
|
Constructing Dates