SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Dates
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jfm
Posting Yak Master

145 Posts

Posted - 04/30/2013 :  11:50:36  Show Profile  Reply with Quote
Hi there,

I have a problem with one column in table_a

In table_a I have two columns: Col_1, Col_2

In Col_1 I have a date value like this one: 30/11/2012
In Col_2 I have a date value like this one: 0012-06-19

The 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

USA
5071 Posts

Posted - 04/30/2013 :  11:59:26  Show Profile  Visit russell's Homepage  Reply with Quote
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.
Go to Top of Page

jfm
Posting Yak Master

145 Posts

Posted - 04/30/2013 :  12:10:36  Show Profile  Reply with Quote
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 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.

Go to Top of Page

jfm
Posting Yak Master

145 Posts

Posted - 04/30/2013 :  12:19:44  Show Profile  Reply with Quote
Or,

I have work in another query in which have the columns as follows:

Col_1 : 10-2012
Col_2: 11-12

Im taking the month and year. Now as you remember the date in column 2 was:
0012-06-19

So 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-2012

How 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.

Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5071 Posts

Posted - 04/30/2013 :  14:13:59  Show Profile  Visit russell's Homepage  Reply with Quote

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)
	)
Go to Top of Page

jfm
Posting Yak Master

145 Posts

Posted - 05/01/2013 :  05:03:14  Show Profile  Reply with Quote
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 you

quote:
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)
	)


Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5071 Posts

Posted - 05/01/2013 :  12:17:30  Show Profile  Visit russell's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000