Convert some 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

Convert some dates

Author

Topic

jfm
Posting Yak Master

145 Posts

Posted - 2013-05-01 : 05:21:49

Hi there,

I have two columns in my table_a. Both are date_col.

In some cases in Col_date_1 my values are given like this: 11(YYYY)- Dec

In Col_date_2 my values are given like this: Dec- 12(YYYY)

But I need all the dates from Col_date_1 to have the same format:

from 11(YYYY)- Dec to Dec -11(YYYY)

Any tips?

Thanks

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2013-05-01 : 05:43:34

Use datetime fields for dates, then you won't have these type of issues and searches will be a lot faster.

jfm
Posting Yak Master

145 Posts

Posted - 2013-05-01 : 06:08:04

Select
cast (c_S as varchar (1) ) as c_S
,(cast(tt_Date as datetime) as tt_date
,O_c
,(cast(I_Date as datetime) as I_Date
,(cast(year(I_Date) as varchar(4))+ '-' + cast(month(I_Date) as varchar(2))) as G
,(cast(year(S_Date) as varchar(4))+ '-' + cast(month(S_Date) as varchar(2))) as S_M
,cast(DATEDIFF(month,I_Date,tt_Date) as varchar (4)) as PP

INTO FINAL_TABLE
FROM date

I have error:

msg 156,level 16, state 1, line 4

If instead of using datetime i use date:

Select
cast (c_S as varchar (1) ) as c_S
,(cast(tt_Date as date) as tt_date
,O_c
,(cast(I_Date as date) as I_Date
,(cast(year(I_Date) as varchar(4))+ '-' + cast(month(I_Date) as varchar(2))) as G
,(cast(year(S_Date) as varchar(4))+ '-' + cast(month(S_Date) as varchar(2))) as S_M
,cast(DATEDIFF(month,I_Date,tt_Date) as varchar (4)) as PP

INTO FINAL_TABLE
FROM date

I have the extraction without errors but still some rows from Col_tt_date are not converted properly and still in reverse.

I dont know what to do,

Thank you

quote:
Originally posted by RickD

Use datetime fields for dates, then you won't have these type of issues and searches will be a lot faster.

jfm
Posting Yak Master

145 Posts

Posted - 2013-05-01 : 07:32:12

I have extracted the wrong dates into a new table.

Now I just need to update the table and convert the date.

Any tip for the query.

No clue.

Thank you

quote:
Originally posted by RickD

Use datetime fields for dates, then you won't have these type of issues and searches will be a lot faster.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources