Sql Query Help

[Bairagi From Bapatla]

[left]2009-03-05 11:59:30.000 (yyyy-mm-dd time) ila ee format lo unna dhanni just naku 200903(yyyymm) ila ravalante query ela rayali.....[/left]


[left]Plz help[/left]

[chandrabhai2]

To_ date(date,'yyyymmdd')

[Bairagi From Bapatla]

sarigga ardham kaledhu baa....
[color=#282828][font=helvetica, arial, sans-serif][left]2009-03-05 11:59:30.000 (yyyy-mm-dd time) -----> 200903 (output ila ravali baa)[/left][/font][/color]

[ChowMein]

[quote name='Bairagi From Bapatla' timestamp='1335296647' post='1301681788']
sarigga ardham kaledhu baa....
[left]2009-03-05 11:59:30.000 (yyyy-mm-dd time) -----> 200903 (output ila ravali baa)[/left]

[/quote]

[color=#282828][font=helvetica, arial, sans-serif]substr(To_ date(date,'yyyymmdd'),1,6) [/font][/color]

[Bairagi From Bapatla]

ala chesthe raledhu baa...'to_date' is not a recognized function name ani vasthundi....

[dotnetrockz]

[color=blue]SELECT[/color][color=black] [/color][color=fuchsia]convert[/color][color=gray]([/color][color=blue]varchar[/color][color=gray],[/color][color=black] [/color][color=fuchsia]getdate[/color][color=gray](),[/color][color=black] 112[/color][color=gray])[/color]

[dotnetrockz]

try this

[color="#0000ff"] [/color]
[color="#0000ff"][color="#0000ff"][size="2"]select[/size][/color][/color][size="2"] [color="#ff00ff"][color="#ff00ff"]Replace[/color][/color][color="#808080"][color="#808080"]([/color][/color][color="#ff00ff"][color="#ff00ff"]SUBSTRING[/color][/color][color="#808080"][color="#808080"]([/color][/color][color="#ff0000"][color="#ff0000"]'2009-03-05 11:59:30.000'[/color][/color][color="#808080"][color="#808080"],[/color][/color][color="#000000"]1[/color][color="#808080"][color="#808080"],[/color][/color][color="#000000"]10[/color][color="#808080"][color="#808080"]),[/color][/color][color="#ff0000"][color="#ff0000"]'-'[/color][/color][color="#808080"][color="#808080"],[/color][/color][color="#ff0000"][color="#ff0000"]''[/color][/color][color="#808080"][color="#808080"])[/color][/color][/size]

[jaffanajeffada]

oracle aa or SQL server aa?

[dotnetrockz]

[img]http://i40.tinypic.com/301kms0.jpg[/img]

[palkabalapam]

[quote name='ChowMein' timestamp='1335296776' post='1301681804']

[color=#282828][font=helvetica, arial, sans-serif]substr(To_ date(date,'yyyymmdd'),1,6) [/font][/color]
[/quote]
This will work
[quote name='Nissan' timestamp='1335298514' post='1301681972']
try this


[color=#0000ff][color=#0000ff][size=2]select[/size][/color][/color][size=2] [color=#ff00ff][color=#ff00ff]Replace[/color][/color][color=#808080][color=#808080]([/color][/color][color=#ff00ff][color=#ff00ff]SUBSTRING[/color][/color][color=#808080][color=#808080]([/color][/color][color=#ff0000][color=#ff0000]'2009-03-05 11:59:30.000'[/color][/color][color=#808080][color=#808080],[/color][/color][color=#000000]1[/color][color=#808080][color=#808080],[/color][/color][color=#000000]10[/color][color=#808080][color=#808080]),[/color][/color][color=#ff0000][color=#ff0000]'-'[/color][/color][color=#808080][color=#808080],[/color][/color][color=#ff0000][color=#ff0000]''[/color][/color][color=#808080][color=#808080])[/color][/color][/size]
[/quote]
i think not 1,10....use 1,7

if i'm wrong

[2pac]

to_char(date_field,'yyyymmdd')

[dotnetrockz]

[quote name='palkabalapam' timestamp='1335298831' post='1301682001']
This will work

i think not 1,10....use 1,7

if i'm wrong
[/quote]

(1,10)--->2009-03-05..total 10 chars..in that { '-' }needs to be replaced......so i used replace fn////

okavela '-' lekpaothe nuvvu cheppinattu 1,7 use cheyyachu..

[150bryant]

good post nissan

[Bairagi From Bapatla]

[quote name='jaffanajeffada' timestamp='1335298658' post='1301681986']
oracle aa or SQL server aa? [i][color=#ff0000]SQL server baa....[/color][/i]
[/quote]
[quote name='Nissan' timestamp='1335298514' post='1301681972']
[i][color=#ff0000]Matter entatnte 2tables join cheyali oka table lo [/color][/i][color=#FF0000][size=2]'2009-03-05 11:59:30.000' ee format lo undi deeni vere table lo 200903 ila unna danitho join cheyali..... so ela convert cheyali...[/size][/color]


[color=#0000ff][size=2]select[/size][/color][size=2] [color=#ff00ff]Replace[/color][color=#808080]([/color][color=#ff00ff]SUBSTRING[/color][color=#808080]([/color][color=#ff0000]'2009-03-05 11:59:30.000'[/color][color=#808080],[/color][color=#000000]1[/color][color=#808080],[/color][color=#000000]10[/color][color=#808080]),[/color][color=#ff0000]'-'[/color][color=#808080],[/color][color=#ff0000]''[/color][color=#808080])[/color][/size][color=#808080][size=2]​[/size][/color]
[/quote]
[quote name='2pac' timestamp='1335298936' post='1301682008']
to_char(date_field,'yyyymmdd')
[/quote]

[dotnetrockz]

[quote name='Bairagi From Bapatla' timestamp='1335300331' post='1301682160']
[/quote]


suppose
table1 col date
--------
'[color=#ff0000][size=2]'2009-03-05 11:59:30.000'[/size][/color]

[color=#ff0000][size=2]create new temp table...[/size][/color]
[color=#ff0000][size=2]create #temptable[/size][/color]
[color=#ff0000][size=2]([/size][/color]
[color=#ff0000][size=2]date varchar(8)[/size][/color]
[color=#ff0000][size=2])[/size][/color]
[color=#ff0000][size=2]insert into #temptable(date)[/size][/color]
[color=#ff0000][size=2][color=#0000ff][color=#0000ff][size=2]select[/size][/color][/color][size=2] [color=#ff00ff][color=#ff00ff]Replace[/color][/color][color=#808080][color=#808080]([/color][/color][color=#ff00ff][color=#ff00ff]SUBSTRING[/color][/color][color=#808080][color=#808080]([/color][/color][color=#ff0000][color=#ff0000]'2009-03-05 11:59:30.000'[/color][/color][color=#808080][color=#808080],[/color][/color][color=#000000]1[/color][color=#808080][color=#808080],[/color][/color][color=#000000]10[/color][color=#808080][color=#808080]),[/color][/color][color=#ff0000][color=#ff0000]'-','')[/color][/color][/size][/size][/color]



[color=#ff0000][size=2]table2:col date[/size][/color]
[color=#ff0000][size=2] 20090305[/size][/color]


[color=#ff0000][size=2]select * from table1[/size][/color]
[color=#ff0000][size=2]inner join #temptable[/size][/color]
[color=#ff0000][size=2]on table1.date=#temptable.date[/size][/color]