Dates - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Dates

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_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

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

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

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources