Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
jfm
Posting Yak Master
145 Posts |
Posted - 2013-04-30 : 11:50:36
|
Hi there, I have a problem with one column in table_a In table_a I have two columns: Col_1, Col_2In Col_1 I have a date value like this one: 30/11/2012In Col_2 I have a date value like this one: 0012-06-19The matter is that I need to create Col_3 that must contain Col_1 - Col_2 so I can see the differences in months between them. Any tip? Thanks |
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-04-30 : 11:59:26
|
yet another example of why dates should be stored using a proper data type.You're going to have to do string manipulation and convert to a date or datetime datatype, then do date math against them. Hopefully there aren't invalid values in there. |
|
|
jfm
Posting Yak Master
145 Posts |
Posted - 2013-04-30 : 12:10:36
|
Thanks Russell. The matter is that the data it was given like that. So I will need to process this data directly from my table_a .. In the design of the table, both columns are using a date data type. What can i do?Thank youquote: Originally posted by russell yet another example of why dates should be stored using a proper data type.You're going to have to do string manipulation and convert to a date or datetime datatype, then do date math against them. Hopefully there aren't invalid values in there.
|
|
|
jfm
Posting Yak Master
145 Posts |
Posted - 2013-04-30 : 12:19:44
|
Or, I have work in another query in which have the columns as follows: Col_1 : 10-2012Col_2: 11-12 Im taking the month and year. Now as you remember the date in column 2 was: 0012-06-19So now i will need either to remove the 20 from Col_1 like this: 10-12 Or add in Col_2 a 20 in the date: 11-2012How can I do it? Thank you quote: Originally posted by russell yet another example of why dates should be stored using a proper data type.You're going to have to do string manipulation and convert to a date or datetime datatype, then do date math against them. Hopefully there aren't invalid values in there.
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-04-30 : 14:13:59
|
[code]declare @dt char(10)declare @dt2 char(10)set @dt = '0012-06-19'set @dt2 = '30/11/2012'SELECT CONVERT(DATE, Convert(char(4), Convert(int, LEFT(@dt, 4)) + 2000) + SUBSTRING(@dt, 6, 2) + RIGHT(@dt, 2) ) SELECT CONVERT(DATE, RIGHT(@dt2, 4) + SUBSTRING(@dt2, 4, 2) + LEFT(@dt2, 2) )[/code] |
|
|
jfm
Posting Yak Master
145 Posts |
Posted - 2013-05-01 : 05:03:14
|
Thank you Russell,Its working this query. But how can I apply this query to a complete column where in some cases the dates need to be modified to do the extraction? Thank youquote: Originally posted by russell
declare @dt char(10)declare @dt2 char(10)set @dt = '0012-06-19'set @dt2 = '30/11/2012'SELECT CONVERT(DATE, Convert(char(4), Convert(int, LEFT(@dt, 4)) + 2000) + SUBSTRING(@dt, 6, 2) + RIGHT(@dt, 2) ) SELECT CONVERT(DATE, RIGHT(@dt2, 4) + SUBSTRING(@dt2, 4, 2) + LEFT(@dt2, 2) )
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-05-01 : 12:17:30
|
Just apply the logic to the column instead of the variables.SELECT CONVERT(DATE, RIGHT(@dt2, 4) + SUBSTRING(ColumnName, 4, 2) + LEFT(@dt2, 2) ) etc |
|
|
|
|
|
|
|