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
 Convert some dates
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jfm
Posting Yak Master

145 Posts

Posted - 05/01/2013 :  05:21:49  Show Profile  Reply with Quote
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

United Kingdom
3608 Posts

Posted - 05/01/2013 :  05:43:34  Show Profile  Reply with Quote
Use datetime fields for dates, then you won't have these type of issues and searches will be a lot faster.
Go to Top of Page

jfm
Posting Yak Master

145 Posts

Posted - 05/01/2013 :  06:08:04  Show Profile  Reply with Quote
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.

Go to Top of Page

jfm
Posting Yak Master

145 Posts

Posted - 05/01/2013 :  07:32:12  Show Profile  Reply with Quote
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.

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.75 seconds. Powered By: Snitz Forums 2000